Rechercher dans le manuel MySQL
2.11.4 Copying MySQL Databases to Another Machine
In cases where you need to transfer databases between different architectures, you can use mysqldump to create a file containing SQL statements. You can then transfer the file to the other machine and feed it as input to the mysql client.
Use mysqldump --help to see what options are available.
The easiest (although not the fastest) way to move a database between two machines is to run the following commands on the machine on which the database is located:
mysqladmin -h 'other_hostname' create db_name
mysqldump db_name | mysql -h 'other_hostname' db_name
If you want to copy a database from a remote machine over a slow network, you can use these commands:
mysqladmin create db_name
mysqldump -h 'other_hostname' --compress db_name | mysql db_name
You can also store the dump in a file, transfer the file to the target machine, and then load the file into the database there. For example, you can dump a database to a compressed file on the source machine like this:
mysqldump --quick db_name | gzip > db_name.gz
Transfer the file containing the database contents to the target machine and run these commands there:
mysqladmin create db_name
gunzip < db_name.gz | mysql db_name
You can also use mysqldump and
mysqlimport to transfer the database. For large
tables, this is much faster than simply using
mysqldump. In the following commands,
DUMPDIR
represents the full path name
of the directory you use to store the output from
mysqldump.
First, create the directory for the output files and dump the database:
mkdir DUMPDIR
mysqldump --tab=DUMPDIR db_name
Then transfer the files in the DUMPDIR
directory to some corresponding directory on the target machine
and load the files into MySQL there:
mysqladmin create db_name # create database
cat DUMPDIR/*.sql | mysql db_name # create tables in database
mysqlimport db_name DUMPDIR/*.txt # load data into tables
Do not forget to copy the mysql
database
because that is where the grant tables are stored. You might have
to run commands as the MySQL root
user on the
new machine until you have the mysql
database
in place.
After you import the mysql
database on the new
machine, execute mysqladmin flush-privileges so
that the server reloads the grant table information.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-copying-databases.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.