Data backup and restoration

Mysqldump implements database backup and recovery based on docker container

Environment Description:
Mydb Source database container mydDB2 New database containerCopy the code
Create the source database container
docker run --name mydb -p 3333:3306 -e MYSQL_ROOT_PASSWORD=123 -v /data/mydb/data:/var/lib/mysql -v /data/mydb/conf/my.cnf:/etc/my.cnf  --privileged=true -d mariadb
Copy the code
Create a new database container
docker run --name mydb -p 3334:3306 -e MYSQL_ROOT_PASSWORD=123 -v /data/mydb2/data:/var/lib/mysql -v /data/mydb2/conf/my.cnf:/etc/my.cnf  --privileged=true -d mariadb
Copy the code
Add data to source database (not required in real world)
docker cp hellodb_innodb.sql mydb:/  	#hellodb_innodb. SQL A SQL statement file
Copy the code
Accessing the source database
docker exec -it mydb bash
mysql -uroot -p< hellodb_innodb.sql	# import data
Check the binary log status
show global variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON 	|
+---------------+-------+
Copy the code
Back up source library data
# mysqldump -uroot -p --all-databases --lock-all-tables --master-data=2 > /all.sql
Enter password: 123

#--lock-all-tables specifies a table lock operation that guarantees no data changes during backup
#--master-data=2 add binary log representation as comment statement to all.sql
Copy the code
See which binary log file the backup started with
Mysql > update all. SQL file to host ~]# pwd
/root
# docker cp mydb:/all.sql .
# less all.sql . CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=8960; .Copy the code
Export these SQL operations to incremental files
# mysqlbinlog --start-position=8960 /var/lib/mysql/mysql-bin.000003 > incre.sql
This is used when restoring to the current point in time with binary logs
Copy the incremental file incre.sql to the host (or try copying myDB to myDB2)
~]# pwd
/root
# docker cp incre.sql mydb2:/ #
Copy the code
Writes the source library changes to disk
> INSERT INTO students (Name,Age,Gender,ClassID,TeacherID) VALUES('foo', 173,'M', 3, 7); > flush privileges;Copy the code

= = = = = = = = = = = = = = = = = = = = = = = = = = data backup complete = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

Restore the data to the new database
Copy the backup. SQL files to the new database
# docker cp incre.sql mydb2:/
# docker exec -it mydb2 bash
Copy the code
Disabling binary Logging
> set sql_bin_log=off;
Disable binary logging at the session level to reduce the IO burden of restore operations
Mysql user must have read permission on all.sql
Copy the code
Import the backup SQL file
> source /all.sql;  The data at this point is only the data at the backup point
Copy the code
Restore to the current point in time with binary logs
mysqlbinlog --start-position=8960 /var/lib/mysql/mysql-bin.000003 > incre.sql
Copy the code

= = = = = = = = = = = = = = = = = = = = = = = = = = = data reduction to complete the = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =