[adsense id=”0514458240″ width=”468″ height=”60″]
Recently I have seen this question raised numerous times. This is particularly important when databases need to be migrated off of one server and on to another. Sometimes a copy of all the database files (while the MySQL server is down) can work. However, it is a better way using MySQL available tools.
It should be noted that the commands and responses are on a MySQL 5.0 server. Different version of the MySQL server may give more or different information but the ideas and concepts remain valid in this document.
Before starting down the path of explaining how this should work we should have a look at any MySQL server. The server contains numerous databases including an internal database used to store internal information such as users, privileges, functions, stored procedures etc. This information is stored in the “mysql” database. If you issue the “show databases” command as the root or admin user you will see the existence of this database:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+
The “mysql” database contains numerous tables that provide information about the tables in a particular database, stored procedures that are created for that database:
mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | func | | help_category | | help_keyword | | help_relation | | help_topic | | host | | proc | | procs_priv | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 17 rows in set (0.00 sec)
The first step is to actually take a copy of the database. To take a backup of a database in MySQL we will use the mysqldump command. This can take numerous parameters but the most important ones are:
$ mysqldump -?
mysqldump Ver 10.13 Distrib 5.5.9, for Linux (i686)
Copyright (c) 2000, 2010, 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.
Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
...
--flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the mysql
database. This option should be used any time the dump
contains the mysql database and any other database that
depends on the data in the mysql database for proper
restore.
-h, --host=name Connect to host.
-p, --password[=name]
Password to use when connecting to server. If password is
not given it's solicited on the tty.
-P, --port=# Port number to use for connection.
-u, --user=name User for login if not current user.
-r, --result-file=name
Direct output to a given file. This option should be used
in systems (e.g., DOS, Windows) that use carriage-return
linefeed pairs (\r\n) to separate text lines. This option
ensures that only a single newline is used.
...
The “-r” and “–flush-privileges” are particularly important. The “-r” flag will generate the dump file in a consistent way across all platforms. So if you wish to dump a database on Windows and import it into a Linux MySQL server this will always work. The “–flush-privileges” appends the MySQL command “FLUSH PRIVILEGES” to the end of the dump file. This will be needed when we dump the users and privilege information into the database.
Now we are ready to start. We are assuming copying a database called “test” from one server to another:
$ mysqldump -u root -r test.sql -p test Enter password:
The result from this command is a file called test.sql which contains every SQL statement for creating a tables, integrity constraints, indexes as well as adding the data into their respective tables.
We also need to take a copy of the “mysql” database which contains all the users accounts and privileges.
$ mysqldump -u root --flush-privileges -r mysql.sql -p mysql Enter password:
Of course the mysql.sql command contains all users and all privileges. This file may be edited to strip out the users you do not wish to copy across and their respective privileges.
To restore these databases on the new server simply issue the following commands in the following order:
$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 432 Server version: 5.0.45 SUSE MySQL RPM Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test; Database changed mysql> source test.sql ... starting loading the database and creating the various tables, indexes etc. mysql> use mysql; Database changed mysql> source mysql.sql ... starting loading the users and privileges
If previously you had not dumped the “mysql” database with the “–flush-privileges” option you can still manually issue the “FLUSH PRIVILEGES” command which makes the privileges immediately recognized by the MySQL server.
mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.02 sec)