mysqldump

Mysqldump is a utility for converting mysql databases to storage.

You basically generate an SQL script that contains all the commands to recreate the database from scratch.

The script export process is as follows: Create a database statement, delete a table, create a table, lock a table, disable an index, insert data, enable an index, and unlock a table.

Quick to use

# game is the library name

Export a library completely
Include database statement, table structure, dataMysqldump -uroot -proot --host=127.0.0.1 --port=3306 -- Databases game > test.sqlExport only the table structureMysqldump -uroot -proot --host=127.0.0.1 --port=3306-d game > test.sql

Export data onlyMysqldump -uroot -proot --host=127.0.0.1 --port=3306 -t game > test.sqlExport the data and structure of multiple tables in a databaseMysqldump -uroot -proot --host=127.0.0.1 game --tables articles Users > test. SQL mysqldump -uroot -proot --host=127.0.0.1 Game articles Users > test.sqlRestore the exported data
mysql -u username -proot databse < backup.sql
Copy the code

parameter

–user=user_name, -u user_name

User name for connecting to the database.

–password=password, -p[password]

The password for connecting to the database, if -p is used, cannot contain Spaces between the password and the password.

–opt or –compact

  • use--optIs equal to using these parameters--add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, and --set-charset, even if not added by default--optParameters are also turned on.
  • use--compactIs equal to using these parameters--skip-add-drop-table, --skip-add-locks, --skip-comments, --skip-disable-keys, and --skip-set-charsetIs disabled by default.

–databases, -B

Specify the name of the library to export.

–all-databases

Export all libraries.

–tables

The –databases or -b option is overridden, specifying that a table is exported and the library option is ignored.

–no-data, -d

No data is exported, only table structures are exported.

Do not dump table contents

–no-create-info, -t

Only data is exported, no TABLE structure is exported, and no CREATE TABLE statement is added.

–no-create-db, -n

Do not add CREATE DATABASE build statements.

–routines, -R

Export stored procedures and custom functions

The instance

Mysqldump -uroot -proot - mysqldump -uroot -proot--host=127.0.0.1 --port=3306 -- Databases game > test.sql

CREATE DATABASE / *! 32312 IF NOT EXISTS*/ `game` / *! 40100 DEFAULT CHARACTER SET latin1 */;

USE `game`;

--
-- Table structure for table `address`
--

DROP TABLE IF EXISTS `address`;
CREATE TABLE `address`(...).LOCK TABLES `address` WRITE;
/ *! 40000 ALTER TABLE `address` DISABLE KEYS */;
INSERT INTO./ *! 40000 ALTER TABLE `address` ENABLE KEYS */;
UNLOCK TABLES; . -d mysqldump -uroot -proot -d mysqldump -uroot -proot -d mysqldump -uroot -proot--host=127.0.0.1 --port=3306 -d game > test.sql

DROP TABLE IF EXISTS `address`;
/ *! 40101 SET @saved_cs_client = @@character_set_client */;
/ *! 40101 SET character_set_client = utf8 */;
CREATE TABLE `address`(...). Mysql > insert mysqldump -uroot -proot; mysql > insert mysqldump -uroot -proot--host=127.0.0.1 --port=3306 -t game > test.sql

LOCK TABLES `address` WRITE;
/ *! 40000 ALTER TABLE `address` DISABLE KEYS */;
/ *! 40000 ALTER TABLE `address` ENABLE KEYS */;
UNLOCK TABLES; Mysqldump-uroot-proot; // Export the table structure and data of a database, excluding the database construction statement mysqldump-uroot-proot--host=127.0.0.1 --port=3306 game > test.sqlMysqldump-uroot-proot; // Select * from mysqldump-uroot-proot--host=127.0.0.1 -B game game2 > test.sql// Export the structure of multiple databases, including the database construction statement mysqldump-uroot-proot--host=127.0.0.1 -d -B game game2 > test.sql// Export data from multiple databases, including the database construction statement mysqldump -uroot -proot--host=127.0.0.1 -t -B game game2 > test.sql
Copy the code

Exporting a table

Mysqldump-uroot-proot; // Select * from mysqldump-uroot-proot--host=127.0.0.1 game articles > test.sqlMysqldump -uroot -proot--host=127.0.0.1 game --tables articles users > test.sql
mysqldump -uroot -proot --host=127.0.0.1 game articles users > test.sqlMysqldump -uroot -proot--host=127.0.0.1 -d game articles users > test.sqlMysqldump -uroot -proot--host=127.0.0.1 -t game articles users > test.sql
Copy the code

Table conditional export

Mysqldump -uroot -proot --host=127.0.0.1 --where='id=1' Game articles > test.sqlCopy the code

Ignore a table

--ignore-table database.tableName
Copy the code

Multiple additions ignore multiple tables.

Import a table data script

#The data sourceSrc_user ="root" # user name src_password="root" # password src_host="localhost" # Host src_port="3306" # port src_database="test" # Database name src_table="edu
#Configure the database for data importDst_user ="root" # username dst_password="root" # password dst_host="localhost" # Host dst_port="3306" # Database name mysqldump - host = $src_host - port = $src_port -u $$$src_database src_password src_user - p - tables $src_table | mysql --host=$dst_host --port=$dst_port -u$dst_user -p$dst_password $dst_databaseCopy the code

Backup script

#! /bin/bash

#Modify the following configuration information by yourselfMysql_user ="root" # mysql_password="root" # mysql_host="localhost" mysql_port="3306" Mysql_charset ="utf8mb4" # backup_db_arr=("db1" "db2") # Backup_location =/var/ WWW /mysql # Backup_location =/var/ WWW /mysql Expire_backup_delete ="OFF" # Expire_days =3 # Expire_days =3 This parameter is valid only when expire_BACKUP_DELETE is enabled
#The following changes are not required at the beginning of this lineBackup_Ymd = 'date +%Y-%m-% D' # backup_Ymd= 'date +%Y-%m-% D' # Backup_3AGO = 'date -d '3 days ago' +%Y-%m-% D '#3 days ago Backup_dir =$backup_location/$backup_Ymd # welcome_msg="Welcome to use MySQL backup tools! # welcome
#Check whether MYSQL is started. If MYSQL is not started, the backup exits
mysql_ps=`ps -ef |grep mysql |wc -l`
mysql_listen=`netstat -an |grep LISTEN |grep $mysql_port|wc -l`
if [ [$mysql_ps == 0] -o [$mysql_listen == 0] ]; then
        echo "ERROR:MySQL is not running! backup stop!"
        exit
else
        echo $welcome_msg
fi

#Connect to the mysql database. If the connection fails, the backup exitsmysql -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password <<end use mysql; select host,user from user where user='root' and host='localhost'; exit end flag=`echo $? ` if [ $flag != "0" ]; then echo "ERROR:Can't connect mysql server! backup stop!" exit else echo "MySQL connect ok! Please wait......" If ["$backup_db_arr"!= ""]; then #dbnames=$(cut -d ',' -f1-5 $backup_database) #echo "arr is (${backup_db_arr[@]})" for dbname in ${backup_db_arr[@]} do echo "database $dbname backup start..." `mkdir -p $backup_dir` `mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $dbname --default-character-set=$mysql_charset | gzip > $backup_dir/$dbname-$backup_time.sql.gz` flag=`echo $? ` if [ $flag == "0" ]; then echo "database $dbname success backup to $backup_dir/$dbname-$backup_time.sql.gz" else echo "database $dbname backup fail!" fi done else echo "ERROR:No database to backup! If ["$expire_backup_delete" == "ON" -a "$backup_location!= ""]; if ["$expire_backup_delete" == "ON" -a "$backup_location"!= ""]; then #`find $backup_location/ -type d -o -type f -ctime +$expire_days -exec rm -rf {} \; ` `find $backup_location/ -type d -mtime +$expire_days | xargs rm -rf` echo "Expired backup data delete complete!" fi echo "All database backup success! Thank you!" exit fiCopy the code

Modify shell script properties:

chmod 600 /root/mysql_backup.sh
chmod +x /root/mysql_backup.sh
Copy the code

After setting the properties, add the command to crontab to set the automatic backup at 00:00 every day.

00 00 * * * /path/to/mysql_backup.sh
Copy the code

Restore the backup

Mysql -u username -proot databse < backup. SQL -uroot -proot --host=127.0.0.1 --port=33006 < Global. SQL or mysql -u root -p use dbname; source dbname.sqlCopy the code