Shulou(Shulou.com)06/01 Report--

The following mainly introduces how to deal with restart failure after full backup recovery of mysql database xtrabackup. I hope that the handling method of restart failure after full backup recovery of mysql database xtrabackup can bring you practical use, which is also the main purpose of my editing this article. All right, don't talk too much nonsense, let's just read the following.

one。 Full backup recovery instructions

The xtrabackup binary has a xtrabackup-- copy-back option, which copies the backup to the datadir directory of the CVM. The following is the directory of the full backup files specified by-- target-dir and restored to the datadir directory.

1xtrabackup-copy-back-target-dir=/data/backups/

You can use the xtrabackup-- move-back option to restore a backup. This option is similar to xtrabackup-- copy-back, except that it moves the file to the target location instead of copying the file. Because this option deletes backup files, you must use it with caution.

If you do not want to use any of the above options, you can use rsync or cp separately to restore the files. Examples of rsync commands that can be used to restore backups are as follows:

?

1$ rsync-avrp / data/backup/ / var/lib/mysql/

In most cases, you need to change the ownership of the file to mysql before starting the database cloud server. Finally, the existing data has been restored, and you can start the CVM. The permission settings are as follows:

?

1$ chown-r mysql:mysql / var/lib/mysql

Note: the datadir directory must be empty before restoring the backup. In addition, mysql CVM needs to be shut down before performing recovery, and cannot be restored to the datadir directory of the running mysqld instance.

How restore works, please see: mysql Development Advanced Series 44 physical backup and recovery (introduction to hot backup xtrabackup tool)

two。 Full backup recovery case

?

12345 innodb-delete the following two tables before recovery-delete a table (myisam) delete from test.a-- delete a testbackup table (innodb) delete from test.`testbackup`

?

1234Murray-first stop the service [root@hsr data] # service mysql stop-- to delete all data files in the data directory. For security reasons, you can first save the original copy [root@hsr data] # rm-rf / usr/local/mysql/data/*?12-- and use xtrabackup to fully restore [root@hsr data] # xtrabackup-- host=127.0.0.1-- user bkpuser-- password=123456-- copy-back.

-- target-dir=/data/backups/

The recovery is successful as shown in the following figure:

?

12Mel-clear the contents of the mysql error log for later viewing [root@hsr data] #: > / var/log/mariadb/mariadb.log

-- failed to restart the service. Viewing the error log shows: 2018-10-09t05:26:19.082347z 0 [error] innodb: the innodb_system data file 'ibdata1' must be writable. 2018-10-09t06:03:17.407537z 0 [error] innodb: operating system error number 13 in a file operation.

?

123Mel-use perror to view status 13, indicating no permission [root@hsr data] # perror 13os error code 13: permission denied

?

1234Murray-change the ownership relationship and change all files in the data directory to mysql users and mysql user groups. [root@hsr data] # chown-r mysql:mysql.. / data [root@hsr data] # service mysql startstarting mysql. Success!

Finally, the startup was successful, and the deleted data of the two tables came back.

three。 Warning messages in the error log

Information: insecure configuration for-- secure-file-priv: location is accessible to all os users. Solution: secure-file-priv the directory specified by my system is / tmp. Set the users and user groups in this directory to mysql.

?

1 [root@hsr tmp] # chown-r mysql.mysql.. / tmp

Information: insecure configuration for-- pid-file: location'/ usr/local/mysql/data' in the path is accessible to all os users. It means that the directory is not secure and is visible to all users. Solution: set the data directory to mysql user and user group permissions, as well as 770 permissions.

?

12 [root@hsr tmp] # chown-r mysql.mysql / usr/local/mysql/data [root@hsr tmp] # chmod 770 / usr/local/mysql/data

3.3Information: failed to set up ssl because of the following ssl library error: ssl context is not usable without certificate and private key. Fix: reinstall ssl. Locate the mysql_ssl_rsa_setup installation in the bin directory of mysql, as follows:

?

123456789 [root@hsr bin] # pwd/usr/local/mysql/bin

-- install [root@hsr bin] # mysql_ssl_rsa_setup-- after datadir=/usr/local/mysql/data-- is generated, you need to change the pem file in the data directory to mysql [root@hsr data] # chown-r mysql:mysql private_ key.pem [root @ hsr data] # chown-r mysql:mysql public_ key.pem [root @ hsr data] # chown-r mysql:mysql server-cert.pem [root@hsr data] # chown-r mysql:mysql server-key.pem...

Information: timestamp with implicit default value is deprecated. Please use-explicit_defaults_for_timestamp server option (see documentation for more details). The default is off, as shown below:

?

123Mo-solve the problem of adding explicit_defaults_for_timestamp=1 to my.cnf and restarting the service [mysqld] explicit_defaults_for_timestamp=1

Summary: with regard to the file permissions reported by the service startup after mysql recovery, all mysql-related files or directories are set to mysql users and mysql user groups.

For the above about the mysql database xtrabackup full backup recovery after restart failure handling methods, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.