Physical copy backup mysql to other machine to restore

Physical copy backup mysql to other machine to restore

I often encounter situations where mysql data needs to be migrated. Logical export is possible, but it is too slow. Third-party tools can also be used and it is troublesome to operate. The simplest and rude way is to directly copy physical files, which is the fastest and simple. Of course, the premise is The remote server and the target server have the same file system and mysql version. Otherwise, you still can't do this. You can refer to MySQL database migration (copy files directly).

1. There are many ways to copy target database files, you can use ftp, scp, rcp, rsync. Reference: 4.ways to transfer files between linux servers, here I use rsync

[root@localhost mysql]# service mysqld stop [root@localhost mysql]# cp -a/var/lib/mysql/home/mysql

*****Remember to delete the ib_logfile in the data/home/mysql directory (this is to be further verified, it is better to make a backup before the operation) and the .err file

To replace the data directory of the target server, first stop mysql and back up the data directory to prevent operation failure and rollback [root@localhost mysql]# service mysqld stop [root@localhost mysql]# mv/var/lib/mysql/var/lib/mysql_bak

Copy the backup file from the source server to the target server:

[root@localhost lib]# rsync -r/home/mysql root@192.168.153.44 :/var/lib/ root@192.168.153.44's password: skipping non-regular file "mysql/mysql.sock"

Don't forget the parameter -r. You need to enter the password of the target server to copy the file once, which is the password of 192.168.153.44. After copying, go to the target server to see if the file is missing, check the/var/lib/mysql/folder

[root@localhost mysql]# ll -h total usage 1.6G -rw-rw---- 1 mysql mysql 539 October 25 16:18 122-55-slow.log -rw-r----- 1 mysql mysql 56 October 25 16:16 auto.cnf drwx------ 2 mysql mysql 4.0K October 25 16:16 cumcmtre drwx------ 2 mysql mysql 71 October 25 16:16 es -rw -r----- 1 mysql mysql 588M October 25 16:18 ibdata1 -rw-rw---- 1 mysql mysql 6 October 25 16:18 localhost.localdomain.pid -rw-r----- 1 mysql mysql 143 October 25 16:17 localhost-relay-bin.000001 -rw-r----- 1 mysql mysql 29 October 25 16:17 localhost-relay-bin.index drwx------ 2 mysql mysql 4.0K October 25 16:16 mysql -rw-r----- 1 mysql mysql 270K October 25 16:17 mysql-bin.000004 -rw-r----- 1 mysql mysql 214 10 Month 25 16:17 mysql-bin.000005 -rw-r----- 1 mysql mysql 214 October 25 16:17 mysql-bin.000006 -rw-r----- 1 mysql mysql 365 October 25 16:18 mysql-bin.000007 -rw-rw---- 1 mysql mysql 191 October 25 16:18 mysql-bin.000008 -rw-rw---- 1 mysql mysql 95 October 25 16:18 mysql-bin.index srwxrwxrwx 1 mysql mysql 0 October 25 16:18 mysql.sock drwx------ 2 mysql mysql 4.0K October 25 16:16 performance_schema -rw-r--r-- 1 mysql mysql 131 October 25 16:16 RPM_UPGRADE_HISTORY -rw-r--r-- 1 mysql mysql 131 October 25 16:16 RPM_UPGRADE_MARKER-LAST

We see that the data files have been copied

File permission modification [root@localhost mysql]# ll Total usage 602436 -rw-r----- 1 root root 56 October 25 16:16 auto.cnf ... ... -rw-r--r- -1 root root 131 October 25 16:16 RPM_UPGRADE_MARKER-LAST

We see that the authority is the root user of the root group, here we want to authorize the mysql user of the MySQL group

[root@localhost mysql]# chown -R mysql.mysql/var/lib/mysql [root@localhost mysql]# ll total usage 602436 -rw-r----- 1 mysql mysql 56 October 25 16:16 auto .cnf ... ... -rw-r--r-- 1 mysql mysql 131 October 25 16:16 RPM_UPGRADE_MARKER-LAST

After modifying the server_uuid of auto.cnf, start mysql. Confirm that the file is not missing. Modify the server_uuid of auto.cnf. Note that this is to prepare for the setup of the master-slave configuration. If the two servers are also running independently in the future, there will be no master-slave. This relationship does not need to be configured.

[root@localhost ~]# cd/var/lib/mysql [root@localhost mysql]# ls 103-55.err cumcm ib_logfile0 localhost-relay-bin.000001 mysql-bin.000004 mysql-bin.000007 mysql.sock RPM_UPGRADE_MARKER -LAST 103-55-slow.log es ib_logfile1 localhost-relay-bin.index mysql-bin.000005 mysql-bin.000008 performance_schema auto.cnf ibdata1 localhost.localdomain.pid mysql mysql-bin.000006 mysql-bin.index RPM_UPGRADE_HISTORY [root@localhost mysql]# vim auto.cnf file content: [auto] server-uuid=00d7559a-121b-45cc-a124-5d77df0d46ca

[root@localhost mysql]# service mysqld restart Redirecting to/bin/systemctl restart mysqld.service

Start mysql [root@localhost mysql]# service mysqld status Redirecting to/bin/systemctl status mysqld.service mysqld.service-MySQL Percona Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled) Active: inactive (dead) since Thursday 2018-10-25 16:13:21 CST; 4min 46s ago Process: 21358 ExecStartPost=/usr/bin/mysql-systemd post (code=exited, status=0/SUCCESS) Process: 21357 ExecStart =/usr/bin/mysqld_safe (code=exited, status=0/SUCCESS) Process: 21327 ExecStartPre=/usr/bin/mysql-systemd pre (code=exited, status=0/SUCCESS) Main PID: 21357 (code= exited, status=0/SUCCESS)

October 25 16:12:36 localhost.localdomain systemd[1]: Starting MySQL Percona Server... October 25 16:12:37 localhost.localdomain mysqld_safe[21357]: 181025 16:12:37 mysqld_safe Logging to'/var/lib/mysql/103-55.err'. October 25 16:12:37 localhost.localdomain mysqld_safe[21357]: 181025 16:12:37 mysqld_safe Starting mysqld daemon with databases from/.../mysql October 25 16:12:44 localhost.localdomain systemd[1]: Started MySQL Percona Server. October 25 16:13:19 localhost.localdomain systemd[1]: Stopping MySQL Percona Server... October 25 16:13:21 localhost.localdomain mysqld_safe[21357]: 181025 16:13:21 mysqld_safe mysqld from pid file/var/lib/mysql/localhos... ended October 25 16:13:21 localhost.localdomain systemd[1]: Stopped MySQL Percona Server. Hint: Some lines were ellipsized, use -l to show in full.[root@localhost mysql]# service mysqld start Redirecting to/bin/systemctl start mysqld.service

View files after startup

[root@localhost mysql]# ll -h Total usage 1.6G -rw-r----- 1 mysql root 105K October 25 16:18 104443-55.err ... ... -rw-r-- r-- 1 mysql mysql 131 October 25 16:16 RPM_UPGRADE_MARKER-LAST

We see that the ib_logfile0 and .err files have also been opened to open the database without any problems. The user account must use the account of the remote server. The target server has been overwritten.