Wednesday, 14 May 2014

Migrating a MySQL Database To A New Red Hat Host

The location of MySQL Data is stored in /etc/my.cnf. By default, the data is stored locally in /var/lib/mysql.

To change the location of the data:

This example shows a migration from rose to martha. It assumes that the destination server has been set up with a standard LAMP environment.

Preparation

1) Log into mysql on the original host
mysql -h rose -u root -p 2) Add a new root user for the new host mysql> CREATE USER 'root'@'martha' IDENTIFIED BY '<RootPassword>';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'martha' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
where <RootPassword> is the root password

3) Check that the new user has been added. This is included in the 'mysql' database. mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select host, user, password from user;
+---------------------+------------+-------------------------------------------+
| host                | user       | password                                  |
+---------------------+------------+-------------------------------------------+
| localhost           | root       | *1CF65C563AC2756B0409CB694208C3F2DAC5E7EA |
| rose.com            | root       |                                           |
| 127.0.0.1           | root       |                                           |
| ::1                 | root       |                                           |
| localhost           |            |                                           |
| rose.com            |            |                                           |
| localhost           | ben        | 235814907f27996c                          |
| localhost           | test_admin | 70de51425df9d787                          |
| localhost           | jamie      | 2a8bf64a7c1bffc4                          |
| %                   | jamie      | 2a8bf64a7c1bffc4                          |
| %                   | polly      | 0d49ee5a14e0b5d7                          |
| localhost           | polly      | 0d49ee5a14e0b5d7                          |
| martha              | root       | 1afe817735574e3d                          |
+---------------------+------------+-------------------------------------------+
13 rows in set (0.00 sec)
4) Exit from MySQL mysql> quit
Bye

Close MySQL on the Old Host

5) Make sure all users are off the database

6) Stop mysql /etc/init.d/mysqld stop

Open MySQL on the New Host

7) On the new host, edit /etc/my.cnf, and change the value of datadir to point at the location of the data. If the data is moving, qv Change the Location of MySQL Data Storage

8) Restart mysql /etc/init.d/mysqld start 9) Open MySQL and test that databases can be seen [root@martha testdevdb]#  mysql -h martha -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.61 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| polly              |
| jamie              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.04 sec)

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select host, user, password from user;
+---------------------+------------+-------------------------------------------+
| host                | user       | password                                  |
+---------------------+------------+-------------------------------------------+
| localhost           | root       | *1CF65C563AC2756B0409CB694208C3F2DAC5E7EA |
| rose.com            | root       |                                           |
| 127.0.0.1           | root       |                                           |
| ::1                 | root       |                                           |
| localhost           |            |                                           |
| rose.com            |            |                                           |
| localhost           | ben        | 235814907f27996c                          |
| localhost           | test_admin | 70de51425df9d787                          |
| localhost           | jamie      | 2a8bf64a7c1bffc4                          |
| %                   | jamie      | 2a8bf64a7c1bffc4                          |
| %                   | polly      | 0d49ee5a14e0b5d7                          |
| localhost           | polly      | 0d49ee5a14e0b5d7                          |
| martha              | root       | 1afe817735574e3d                          |
+---------------------+------------+-------------------------------------------+
13 rows in set (0.00 sec)

mysql> quit
Bye

Reset All Root Passwords

Reset all root passwords to the standard. This can only be done by putting MySQL into skip-grant-tables mode. (qv http://www.howtoforge.com/setting-changing-resetting-mysql-root-passwords)

10) Stop MySQL and restart in skip-grant-tables mode [root@martha testdevdb]# /etc/init.d/mysqld stop
Stopping mysqld:                                           [  OK  ]
[root@martha testdevdb]# mysqld_safe --skip-grant-tables &
[1] 19498
[root@martha testdevdb]# 130703 14:34:16 mysqld_safe Logging to '/var/log/mysqld.log'.
130703 14:34:16 mysqld_safe Starting mysqld daemon with
databases from /testdir/testdev/testdevdb
11) Login to MySQL [root@martha testdevdb]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.61 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
12) Select the 'mysql' database mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
13) Set the root password for all hosts mysql> update user set password=PASSWORD("<RootPassword>") where User='root';
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0
where <RootPassword> is the root password
14) Reload the privileges from the grant tables, then exit mysql mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye
15) Stop MySQL and restart without skip-grant-tables [root@martha testdevdb]# /etc/init.d/mysqld stop
130703 14:35:56 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
Stopping mysqld:                                           [  OK  ]
[1]+  Done                    mysqld_safe --skip-grant-tables
[root@martha testdevdb]# /etc/init.d/mysqld start
Starting mysqld:                                           [  OK  ]
16) Check that the root users have been updated [root@martha testdevdb]#  mysql -h martha -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.61 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select host, user, password from user;
+---------------------+------------+-------------------------------------------+
| host                | user       | password                                  |
+---------------------+------------+-------------------------------------------+
| localhost           | root       | *B85234EF763B03A804D8ACBA611FDAB53B80723A |
| rose.com            | root       | *B85234EF763B03A804D8ACBA611FDAB53B80723A |
| 127.0.0.1           | root       | *B85234EF763B03A804D8ACBA611FDAB53B80723A |
| ::1                 | root       | *B85234EF763B03A804D8ACBA611FDAB53B80723A |
| localhost           |            |                                           |
| rose.com            |            |                                           |
| localhost           | ben        | 235814907f27996c                          |
| localhost           | test_admin | 70de51425df9d787                          |
| localhost           | jamie      | 2a8bf64a7c1bffc4                          |
| %                   | jamie      | 2a8bf64a7c1bffc4                          |
| %                   | polly      | 0d49ee5a14e0b5d7                          |
| localhost           | polly      | 0d49ee5a14e0b5d7                          |
| martha              | root       | *B85234EF763B03A804D8ACBA611FDAB53B80723A |
+---------------------+------------+-------------------------------------------+
13 rows in set (0.00 sec)

mysql> quit
Bye

Force Compatibility

17) Check that all the tables are compatable with the version of MySQL [root@martha testdevdb]# mysql_upgrade -p -u root
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck with default connection arguments
Running 'mysqlcheck with default connection arguments
jamie.559_MT4                                      OK
jamie.559_MT5                                      OK
...
...
...
Running 'mysql_fix_privilege_tables'...
WARNING: NULL values of the 'character_set_client' column ('mysql.proc' table) have
been updated with a default value (latin1). Please verify if necessary.
WARNING: NULL values of the 'collation_connection' column ('mysql.proc' table) have
been updated with a default value (latin1_swedish_ci). Please verify if necessary.
WARNING: NULL values of the 'db_collation' column ('mysql.proc' table) have been
updated with default values. Please verify if necessary.
OK

[root@martha testdevdb]#  mysql -h localhost -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 181
Server version: 5.1.61 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'martha' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> Bye
[root@martha testdevdb]# logout
Connection to martha closed.
root@system /testdir/testdev/testdevdb> exit
#