Tag Archives: MySQL

Linux

Change WordPress URLs in MySQL Database

Leave a comment

Introduction

In this tutorial, How to Change WordPress URLs in MySQL Database using the command line. WordPress uses the MySQL database to store all its data, including site URLs.

Determining the Name of WordPress MySQL Database

WordPress stores the MySQL database name and its credentials in the wp-config.php file. Example below

[devopsroles@server1 ~]$ egrep -A 10 DB_NAME /Wordpress/web/wp-config.php 
define('DB_NAME', 'wordpressdb');

/** MySQL database username */
define('DB_USER', 'HuuPV');

/** MySQL database password */
define('DB_PASSWORD', 'devopsroles.com');

/** MySQL hostname */
define('DB_HOST', 'localhost');

Change WordPress URLs

I will change from the Old site: https://linuxoperatingsystem.net to the New site: http://192.168.122.229

Determining the site URL currently with the command below

mysql> SELECT * FROM wp_options WHERE option_name='siteurl';
+-----------+-------------+----------------------------------+----------+
| option_id | option_name | option_value                     | autoload |
+-----------+-------------+----------------------------------+----------+
|         1 | siteurl     | https://linuxoperatingsystem.net | yes      |
+-----------+-------------+----------------------------------+----------+
1 row in set (0.00 sec)

mysql>

Command-line change WordPress URLs in MySQL Database

mysql -u root -p Database_Name
mysql> UPDATE wp_options SET option_value = replace(option_value, 'https://linuxoperatingsystem.net', 'http://192.168.122.229') WHERE option_name = 'home' OR option_name = 'siteurl';
mysql> UPDATE wp_posts SET guid = replace(guid, 'https://linuxoperatingsystem.net','http://192.168.122.229');
mysql> UPDATE wp_posts SET post_content = replace(post_content, 'https://linuxoperatingsystem.net', 'http://192.168.122.229');
mysql> UPDATE wp_postmeta SET meta_value = replace(meta_value,'https://linuxoperatingsystem.net','http://192.168.122.229');

The output is as below:

mysql> show databases;
+-------------------------+
| Database                |
+-------------------------+
| information_schema      |
| mysql                   |
| performance_schema      |
| sys                     |
| wordpressdb             |
+-------------------------+
6 rows in set (0.00 sec)

mysql> use wordpressdb;
Database changed
mysql> 


mysql> UPDATE wp_options SET option_value = replace(option_value, 'https://linuxoperatingsystem.net', 'http://192.168.122.229') WHERE option_name = 'home' OR option_name = 'siteurl';
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> UPDATE wp_posts SET guid = replace(guid, 'https://linuxoperatingsystem.net','http://192.168.122.229');
Query OK, 43 rows affected (0.03 sec)
Rows matched: 43  Changed: 43  Warnings: 0

mysql> UPDATE wp_posts SET post_content = replace(post_content, 'https://linuxoperatingsystem.net', 'http://192.168.122.229');
Query OK, 29 rows affected (0.01 sec)
Rows matched: 43  Changed: 29  Warnings: 0

mysql> UPDATE wp_postmeta SET meta_value = replace(meta_value,'https://linuxoperatingsystem.net','http://192.168.122.229');
Query OK, 0 rows affected (0.00 sec)
Rows matched: 73  Changed: 0  Warnings: 0

Conclusion

If you’re managing a WordPress site, you may find yourself needing to change the URLs directly in the MySQL database, especially after a migration or domain change. This process involves accessing your MySQL database through a command line interface and using SQL commands to update the site URL across different tables such as wp_options, wp_posts, and wp_postmeta. It’s a crucial skill for maintaining the integrity of your WordPress site links. Always back up your database before making such changes to avoid any potential data loss. For detailed guidance, consider visiting DevOpsRoles for tutorials.

Database Administrator

Ultimate MySQL Cheat Sheet: Essential Commands You Need to Know

Leave a comment

Introduction

Welcome to the MySQL Cheat Sheet, a valuable resource for anyone looking to enhance their command line skills with MySQL. This comprehensive guide covers a range of commands, from basic operations like database creation and deletion to more complex tasks such as backing up and restoring databases.

In this tutorial, I will write a MySQL Cheat sheet by command line. How to Reset Root Password in MySQL? How to backup and restore databases, and so forth in MySQL databases.

  • MySQL Database Commands Cheat Sheet:
    • A comprehensive list of commands for database creation, modification, and management.
  • MySQL Command Line Client Cheat Sheet:
    • Essential commands for initiating and interacting with MySQL through the command line.
  • Create Table MySQL Cheat Sheet:
    • Syntax and options for creating tables, defining columns, setting data types, and applying constraints.
  • MySQL Connect Cheat Sheet:
    • Commands for connecting to a MySQL database using various authentication methods.

MySQL cheat sheet

At the command line, log in to MySQL as the root user:

mysql -u root -p

How to create and delete MySQL DATABASE

CREATE DATABASE DBName
CREATE DATABASE DBName CHARACTER SET utf8
DROP DATABASE DBName

Backup DATABASE to SQL File in MySQL

mysqldump -u Username -p DBName > sampledatabasename_backup.sql

Restore from backup SQL File in MySQL

mysql - u Username -p DBName < sampledatabasename_backup.sql

MySQL select query

SELECT * FROM table
SELECT * FROM table1, table2, ...
SELECT field1, field2, ... FROM table1, table2, ...
SELECT ... FROM ... WHERE condition
SELECT ... FROM ... WHERE condition GROUPBY field
SELECT ... FROM ... WHERE condition GROUPBY field HAVING condition2
SELECT ... FROM ... WHERE condition ORDER BY field1, field2
SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC
SELECT ... FROM ... WHERE condition LIMIT 10
SELECT DISTINCT field1 FROM ...
SELECT DISTINCT field1, field2 FROM ...

Users and Privileges in MySQL

GRANT ALL PRIVILEGES ON sampledatabase.* TO 'user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, DELETE ON sampledatabase.* TO 'user'@'localhost' IDENTIFIED BY 'password';
REVOKE ALL PRIVILEGES ON sampledatabase.* FROM 'user'@'hostname'; -- one permission only
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'hostname'; -- To revoke all privileges, which drops all global, database, table, column, and routine privileges for the named user or users

SET PASSWORD = PASSWORD('new_pass')
SET PASSWORD FOR 'user'@'hostname' = PASSWORD('new_pass')
SET PASSWORD = OLD_PASSWORD('new_pass')

DROP USER 'user'@'hostname' -- DROP to delete a user

Reset Root Password in MySQL

$ /etc/init.d/mysql stop
$ mysqld_safe --skip-grant-tables

Open another terminal

$ mysql 
mysql> UPDATE mysql.user SET password=PASSWORD('new_pass') WHERE user='root';

Switch back to the mysqld_safe terminal and kill the process using Control

$ /etc/init.d/mysql start

Conclusion

This MySQL Cheat Sheet is designed to serve as a quick reference for frequently used MySQL commands. Whether you’re a beginner or an experienced developer, these commands are essential tools to efficiently manage your databases. Keep this guide handy to streamline your database operations. I hope will this your helpful. Thank you for reading the DevopsRoles page!