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
#
