Linux系统管理基础项目教程课件 V15-3 备份与恢复MySQL数据库

Linux操作系统备份与恢复MySQL数据库备份与恢复MySQL数据库1．MySQL数据库的备份和恢复（1）使用mysqldump命令备份所有数据库。[root@server～]#mysqldump-uroot-p--lock-all-tables--all-databases--events>mysql_dump.sql#-uroot用于指定要使用的MySQL用户名为root#-p用于提示输入密码*/--lock-all-tables表示将在备份时锁定所有表，确保在备份期间数据库中的数据不会发生变化。这样可以确保备份的一致性，但是可能会导致数据库在备份期间无法执行写操作*/#--all-databases表示备份所有数据库#--events表示导出MySQL数据库中的事件/*>mysql_dump.sql表示将备份的数据库内容输出到名为mysql_dump.sql的文件中。>符号表示将命令的输出重定向到指定的文件中*/[root@server～]#mysqldump-uroot-p--single-transaction--all-databases--events>mysql_dump.sql/*--single-transaction表示在备份过程中使用单个事务，这意味着备份期间数据库将保持一致性，即使在备份过程中进行了写操作也不会影响备份结果。这对于在备份期间会执行读写操作的生产环境非常有用，因为它可以确保备份的一致性*/（2）使用mysqldump命令备份指定的数据库。[root@server～]#mysqldump-uroot-pmy_database--single-transaction--events>mysql_mydatabase.sqlmysql>createdatabasetest_database;QueryOK,1rowaffected(0.03sec)mysql>usetest_database;Databasechangedmysql>showtables;Emptyset(0.00sec)（3）创建test_database数据库用于数据恢复。备份与恢复MySQL数据库（4）依次执行mysql_mydatabase.sql文件中的所有SQL语句，以恢复test_database数据库中的数据。[root@server～]#mysql-uroot-ptest_database<mysql_mydatabase.sqlEnterpassword:（5）查询test_database数据库中的数据信息。mysql>usetest_database;Databasechangedmysql>showtables;+-------------------------+|Tables_in_test_database|+-------------------------+|departments||employees|+-------------------------+2rowsinset(0.01sec)mysql>select*fromemployees;备份与恢复MySQL数据库2．MySQL数据库的克隆数据库克隆需要两台主机，mysqlserver主机作为克隆源主机，IP地址为0；backup作为数据接收主机，IP地址为1。两台主机都已经安装好mysql-server数据库软件。（1）分别在两台主机上编辑配置文件f，添加克隆参数。[root@mysqlserver~]#vi/etc/f.d/f[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socklog-error=/var/log/mysql/mysqld.logpid-file=/run/mysqld/mysqld.pidplugin-load=mysql_clone.sogtid_mode=ONenforce_gtid_consistency=ON[root@backup~]#vi/etc/f.d/f[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socklog-error=/var/log/mysql/mysqld.logpid-file=/run/mysqld/mysqld.pidplugin-load=mysql_clone.sogtid_mode=ONenforce_gtid_consistency=ON#datadir:指定数据库文件的存储路径

#socket:指定MySQL服务器用于与客户端通信的套接字文件路径#log-error:指定错误日志文件的路径。MySQL服务器将与错误相关的信息记录到该文件中#pid-file:指定PID文件的路径。PID文件中包含MySQL服务器进程的PID#plugin-load:加载特定插件#gtid_mode:用于设置是否启用GTID（GlobalTtansactionIdentifier，全局事务标识）模式。GTID是一种用于跟踪复制事务的机制，启用GTID可以简化复制配置和故障恢复#enforce_gtid_consistency:用于设置是否强制实施GTID一致性。服务器通过仅允许执行可使用GTID进行安全记录的语句来强制实施GTID一致性备份与恢复MySQL数据库（2）分别在两台主机上重启mysqld服务使配置生效。[root@mysqlserver~]#systemctlrestartmysqld[root@backup~]#systemctlrestartmysqld（3）分别在两台主机上查询MySQL服务的克隆功能是否启用。[root@mysqlserver~]#mysql-uroot-p-e"selectplugin_name,plugin_status,plugin_typefrominformation_schema.pluginswhereplugin_name='clone';"Enterpassword:#输入mysql数据库root账户密码+-------------+---------------+-------------+|plugin_name|plugin_status|plugin_type|+-------------+---------------+-------------+|clone|ACTIVE|CLONE|+-------------+---------------+-------------+[root@backup~]#mysql-uroot-p-e"selectplugin_name,plugin_status,plugin_typefrominformation_schema.pluginswhereplugin_name='clone'"Enterpassword:#输入mysql数据库root账户密码+-------------+---------------+-------------+|plugin_name|plugin_status|plugin_type|+-------------+---------------+-------------+|clone|ACTIVE|CLONE|+-------------+---------------+-------------+mysql>SHOWVARIABLESLIKE'gtid_mode';+---------------+-------+|Variable_name|Value|+---------------+-------+|gtid_mode|ON|+---------------+-------+1rowinset(0.00sec)备份与恢复MySQL数据库（4）在克隆源主机mysqlserver上创建用户，并赋予用户权限。mysql>createuser'clone_user1'@'%'identifiedby'RedHat1993.';QueryOK,0rowsaffected(0.03sec)mysql>grantBACKUP_ADMINon*.*to'clone_user1'@'%';QueryOK,0rowsaffected(0.01sec)（5）在克隆源主机mysqlserver上，将克隆源数据目录的内容复制到指定的目标路径中。[root@mysqlserver~]#mysql-uroot-p-e"clonelocaldatadirectory='/var/lib/mysql-files/backup01/';"Enterpassword:[root@mysqlserver~]#ll/var/lib/mysql-files/backup01/total70660drwxr-x---.2mysqlmysql89Mar2021:28'#clone'-rw-r-----.1mysqlmysql3528Mar2021:28ib_buffer_pool-rw-r-----.1mysqlmysql16777216Mar2021:28undo_001-rw-r-----.1mysqlmysql16777216Mar2021:28undo_002……（6）在数据接收主机backup上创建用户，并赋予用户权限。mysql>createuser'clone_user1'@'%'identifiedby'RedHat1993.';QueryOK,0rowsaffected(0.03sec)mysql>grantBACKUP_ADMINon*.*to'clone_user1'@'%';QueryOK,0rowsaffected(0.01sec)（7）在数据接收主机backup上，设置全局变量clone_valid_donor_list的值为'0:3306'，指定允许作为克隆源的MySQL实例的地址和端口。mysql>setglobalclone_valid_donor_list='0:3306';QueryOK,0rowsaffected(0.00sec)#上述命令用于设置克隆源IP地址为0、端口为3306的MySQL实例备份与恢复MySQL数据库（8）在数据接收主机backup上，从克隆源主机mysqlserver上克隆一个MySQL实例。mysql>cloneinstancefromclone_user1@0:3306identifiedby'RedHat1993.'datadirectory='/var/lib/mysql-files/backup01/’;#fromclone_user1@0:3306：指定要克隆的源MySQL实例的连接信息。其中，clone_user1为源MySQL实例的用户名，0为源MySQL实例的IP地址，3306为源MySQL实例的端口号*/#identifiedby'RedHat1993.'：指定用于连接源MySQL实例的密码#datadirectory='/var/lib/mysql-files/backup01/'：指定克隆数据目录的目标路径（9）在数据接收主机backup上检查克隆操作的状态，状态值为“Completed”表示克隆操作已经完成。mysql>selectSTATEfromperformance_schema.clone_status;+-----------+|STATE|+-----------+|Completed|+-----------+1rowinset(0.01sec)（10）在数据接收主机backup上查询克隆操作的二进制日志文件信息。mysql>SELECTBINLOG_FILE,BINLOG_POSITIONFROMperformance_schema.clone_status;+---------------+-----------------+|BINLOG_FILE|BINLOG_POSITION|+---------------+-----------------+|binlog.000002|722|+---------------+-----------------+备份与恢复MySQL数据库（11）在数据接收主机backup上查询克隆后的数据库信息，my_database数据库以及departments、employees表均克隆成功。mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||my_database||mysql||performance_schema||sys|+--------------------+5rowsinset(0.00sec)mysql>usemy_database;Databasechangedmysql>showtables;+-----------------------+|Tables_in_my_database|+-----------------------+|departments||employees|+-----------------------+2rowsinset(0.01sec)备份与恢复MySQL数据库3．MySQL主从数据库配置MySQL主从数据库配置需要两台主机，第1台主机名为mastermysql，IP地址为0；第2台主机名为slavemysql，IP地址为1，两台主机都已经安装好mysql-server数据库软件。MySQL主从复制可将mastermysql主机上的MySQL数据同步到其他slavemysql主机上。（1）在mastermysql主机上，编辑配置文件f，在[mysqld]段中添加参数。[root@mastermysql~]#vi/etc/f.d/f[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socklog-error=/var/log/mysql/mysqld.logpid-file=/run/mysqld/mysqld.pidgtid_mode=ONenforce_gtid_consistency=ONlog-bin=/var/lib/mysql/mysql-bin.logserver-id=101bind-address=0（2）在mastermysql主机上，重启mysqld服务使配置参数生效。[root@mastermysql~]#systemctlrestartmysqld备份与恢复MySQL数据库（3）在slavemysql主机上，编辑配置文件f，在[mysqld]段中添加参数。[root@slavemysql~]#vi/etc/f.d/f[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socklog-error=/var/log/mysql/mysqld.logpid-file=/run/mysqld/mysqld.pidgtid_mode=ONenforce_gtid_consistency=ONserver-id=102log-bin=/var/lib/mysql/mysql-bin.logrelay-log=slavemysql-relay-binrelay-log-index=slavemysql-relay-binlog-replica-updates=ONskip-replica-start=ONmysql>createuser'replication_user1'@'%'identifiedby'RedHat1993.';mysql>grantreplicationslaveon*.*toreplication_user1@'%';mysql>flushprivileges;（4）在slavemysql主机上，重启mysqld服务使配置参数生效。[root@slavemysql~]#systemctlrestartmysqld（5）在mastermysql主机上，创建用户，并赋予其复制权限，重新载入MySQL数据库中的授权表。（6）在mastermysql主机上，将源服务器设置为只读状态。mysql>SET@@GLOBAL.read_only=ON;（7）在slavemysql主机上，将副本服务器设置为只读状态。mysql>SET@@GLOBAL.read_only=ON;备份与恢复MySQL数据库（8）在mastermysql主机上，获取主节点的当前二进制日志文件名、位置以及执行的GTID集合的详细信息。mysql>SHOWMASTERSTATUS\G;***************************1.row***************************File:mysql-bin.000002#表示当前正在写入的二进制日志文件的名称

Position:4264#表示当前写入位置在该二进制日志文件中的偏移量

Binlog_Do_DB:Binlog_Ignore_DB:Executed_Gtid_Set:55aa5c8b-e691-11ee-bd5a-525400635a76:1-291rowinset(0.00sec)（9）在slavemysql主机上，先停止从节点的复制进程，再用指定的配置连接到主节点，从指定的二进制日志文件和位置复制数据。mysql>stopslave;mysql>changemastertomaster_host='0',master_user='replication_user1',master_password='RedHat1993.',master_log_file='mysql-bin.000002',master_log_pos=4264;#master_host='0'：指定主节点的IP地址或主机名#master_user='replication_user1'：指定连接主节点所使用的用户名#master_password='RedHat1993.'：指定连接主节点所使用的密码#master_log_file='mysql-bin.000002'：指定从哪个二进制日志文件开始复制。这里设置为mysql-bin.000002，表示从节点将从该日志文件开始复制#master_log_pos=4264：指定从节点开始复制的二进制日志位置。这里设置为4264，表示从节点将从该位置开始复制主节点的二进制日志（10）在slavemysql主机上，启动从节点的复制进程。从节点会持续监听主节点的二进制日志，并根据主节点的更新进行相应的复制操作，确保从节点的数据与主节点保持一致。mysql>startslave;备份与恢复MySQL数据库（11）获取从节点的复制状态和相关参数的详细信息。mysql>showslavestatus\G***************************1.row***************************Slave_IO_State:WaitingforsourcetosendeventMaster_Host:0Master_User:replication_user1Master_Port:3306Connect_Retry:60Master_Log_File:mysql-bin.000002Read_Master_Log_Pos:4264Relay_Log_File:slavemysql-relay-bin.000002Relay_Log_Pos:326Relay_Master_Log_File:mysql-bin.000002Slave_IO_Running:YesSlave_SQL_Running:YesExec_Master_Log_Pos:4264Relay_Log_Space:541Master_Server_Id:101#Slave_IO_Running：表示从节点的复制IO线程是否正在运行#Slave_SQL_Running：表示从节点的复制SQL线程是否正在运行（12）在mastermysql主机上创建数据库并添加表格数据。[root@mastermysql～]#mysql-uroot-pEnterpasswor