xtrabackup全备
LNMP环境搭建与配置
任务1 安装LNMP，基础配置解析PHP
1. 安装MySQL
（1）卸载mariadb
[root@test ~]# rpm -qa | grep mariadb mariadb-libs-5.5.44-2.el7.centos.x86_64 //查看有无安装mariadb
[root@test ~]# rpm -e --nodeps mariadb-libs-5.5.44-2.el7.centos.x86_64//卸载mariadb
（2）下载
[root@test ~]# cd /opt/ [root@test opt]# wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz --2021-06-16 12:42:22-- https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz Resolving cdn.mysql.com (cdn.mysql.com)... 23.13.185.161 Connecting to cdn.mysql.com (cdn.mysql.com)|23.13.185.161|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 661214270 (631M) [application/x-tar-gz] Saving to: ‘mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz’ 100%[========================>] 661,214,270 1.27MB/s in 5m 37s 2021-06-16 12:48:05 (1.87 MB/s) - ‘mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz’ saved [661214270/661214270]
（3）解压
[root@test opt]# tar zxvf mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
（4）安装和配置
安装依赖
[root@test~]# yum -y install ncurses-devel autoconf
[root@test opt]# groupadd mysql [root@test opt]# useradd -g mysql -s /sbin/nologin -d /usr/local/mysql -MN mysql [root@test opt]# mkdir -p /data/mysql/data [root@test opt]# chown -R mysql:mysql /data/mysql/ [root@test opt]# mv mysql-5.7.32-linux-glibc2.12-x86_64 /usr/local/ [root@test opt]# ln -s /usr/local/mysql-5.7.32-linux-glibc2.12-x86_64/ /usr/local/mysql [root@test opt]# chown -R mysql.mysql /usr/local/mysql [root@test opt]# cd /usr/local/mysql
[root@test mysql]# vi /etc/my.cnf [client] port = 3306 socket = /tmp/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql basedir = /usr/local/mysql datadir = /data/mysql/data port = 3306 socket = /tmp/mysql.sock log-error = error.log slow_query_log_file = slow.log character-set-server = utf8 open_files_limit = 65535 max_connections = 100 max_connect_errors = 100000 lower_case_table_names =1
初始化mysql
[root@test mysql]# /usr/local/mysql/bin/mysqld --initialize
[root@test mysql]# grep 'temporary password' /data/mysql/data/error.log -bash: $'grep\240temporary\240password': command not found [root@localhost mysql]# grep 'temporary password' /data/mysql/data/error.log 2021-06-16T17:58:49.087477Z 1 [Note] A temporary password is generated for root@localhost: =Tb>b1s3ol)5 // 查看mysql密码
（5）复制启动脚本文件
[root@test mysql]# cp support-files/mysql.server /etc/init.d/mysql
（6）添加环境变量
[root@test mysql]# echo "export PATH=/usr/local/mysql/bin:$PATH">>/etc/profile [root@test mysql]# source /etc/profile
（7）启动mysql
[root@test mysql]# service mysql start
Starting MySQL. SUCCESS!
（8）查看你mysql进程
[root@test mysql]# ps -ef | grep mysql root 3212 1 0 14:03 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql/data --pid-file=/data/mysql/data/localhost.localdomain.pid mysql 3454 3212 0 14:03 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=error.log --open-files-limit=65535 --pid-file=/data/mysql/data/localhost.localdomain.pid --socket=/tmp/mysql.sock --port=3306 root 3484 3014 0 14:04 pts/0 00:00:00 grep --color=auto mysql
（9）登录mysql
[root@test mysql]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.32 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
（10）修改mysql密码
mysql> alter user user() identified by 'test'; Query OK, 0 rows affected (0.00 sec)
（11）登录mysql
[root@test mysql]# mysql -uroot -ptest mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.32 MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
2. 安装Nginx
（1）使⽤用Nginx官⽅方提供的rpm包。
[root@test ~]# vi /etc/yum.repos.d/nginx.repo [nginx] name=nginx repo baseurl=http://nginx.org/packages/centos/7/$basearch/ gpgcheck=0 enabled=1
（2）执行yum安装
[root@test ~]# yum -y install nginx
[root@test ~]# systemctl start nginx [root@test ~]# systemctl enable nginx Created symlink from /etc/systemd/system/multi-user.target.wants/nginx.service to /usr/lib/systemd/system/nginx.service.
3. 安装PHP
（1）使用第三方扩展epel源安装php7.2
[root@test ~]# rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm Retrieving https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm warning: /var/tmp/rpm-tmp.XpPo0g: Header V4 RSA/SHA256 Signature, key ID 352c64e5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:epel-release-7-13 ################################# [100%] [root@test ~]# rpm -Uvh https://mirror.webtatic.com/yum/el7/webtatic-release.rpm Retrieving https://mirror.webtatic.com/yum/el7/webtatic-release.rpm warning: /var/tmp/rpm-tmp.vhBR8E: Header V4 RSA/SHA1 Signature, key ID 62e74ca5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:webtatic-release-7-3 ################################# [100%]
（2）清除版本
[root@test ~]# yum -y remove php* Loaded plugins: fastestmirror No Match for argument: php* No Packages marked for removal
（3）安装php
[root@test ~]# yum -y install php72w php72w-cli php72w-fpm php72w-common php72w-devel php72w-embedded php72w-gd php72w-mbstring php72w-mysqlnd php72w-opcache php72w-pdo php72w-xml php72w-redis
（4）启动php-fpm服务
[root@test ~]# systemctl enable php-fpm.service Created symlink from /etc/systemd/system/multi-user.target.wants/php-fpm.service to /usr/lib/systemd/system/php-fpm.service. [root@test ~]# systemctl start php-fpm.service
（7）启动php-fpm
[root@test ~]# service php-fpm start
Redirecting to /bin/systemctl start php-fpm.service
4. 配置nginx支持PHP
[root@test ~]# vi /etc/nginx/conf.d/default.conf server { listen 80; server_name localhost; #access_log /var/log/nginx/host.access.log main; location / { root /soft/code; index index.php index.html index.htm; location ~ \.php$ { fastcgi_pass 127.0.0.1:9000; fastcgi_index index.php; fastcgi_param SCRIPT_FILENAME /soft/code$fastcgi_script_name; include fastcgi_params; } } #添加及修改一下
新建目录
[root@test ~]# mkdir -p /soft/code [root@test ~]# vi /soft/code/info.php <?php phpinfo(); ?>
重启nginx
[root@test ~]# nginx -t nginx: the configuration file /etc/nginx/nginx.conf syntax is ok nginx: configuration file /etc/nginx/nginx.conf test is successful [root@test ~]# nginx -s reload
浏览网页
[root@test ~]# mysql -uroot -ptest mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.32 MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database wordpress; Query OK, 1 row affected (0.08 sec) mysql> GRANT ALL ON wordpress.* TO 'wpuser'@'localhost' IDENTIFIED BY 'password'; Query OK, 0 rows affected, 1 warning (0.08 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.08 sec) mysql> exit
Bye
[root@test ~]# cd /soft/code/ [root@test code]# ls info.php wordpress-5.7.2-zh_CN.tar.gz [root@test code]# [root@test code]# tar xf wordpress-5.7.2-zh_CN.tar.gz
[root@test code]# ls
info.php wordpress wordpress-5.7.2.tar.gz
[root@test code]# cd wordpress
[root@test wordpress]# cp wp-config-sample.php wp-config.php
[root@test wordpress]# vi wp-config.php ...... /** The name of the database for WordPress */ define( 'DB_NAME', 'wordpress' ); /** MySQL database username */ define( 'DB_USER', 'wpuser' ); /** MySQL database password */ define( 'DB_PASSWORD', 'password' ); /** MySQL hostname */ define( 'DB_HOST', '127.0.0.1' ); /** Database Charset to use in creating database tables. */ define( 'DB_CHARSET', 'utf8' ); /** The Database Collate type. Don't change this if in doubt. */ define( 'DB_COLLATE', '' ); .....
浏览器输入http://192.168.137.18/wordpress
输入用户名cool 密码cool 邮箱：admin@163.com 点击安装
任务2 MySQL主从复制-GTID
1. GTID概念
全局事务标识：global transaction identifiers。
GTID与事务一一对应，并且全局唯一ID。
一个GTID在一个服务器上只执行一次。
MySQL-5.6.5开始支持GTID。
GTID组成：
GTID = server_uuid:transaction_id
2. 配置GTID
配置主从my.cnf
1、主my.cnf：
[root@www ~]# vi /etc/my.cnf [client] port = 3306 socket = /tmp/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql basedir = /usr/local/mysql datadir = /data/mysql/data port = 3306 socket = /tmp/mysql.sock log-error = error.log slow_query_log_file = slow.log character-set-server = utf8 open_files_limit = 65535 max_connections = 100 max_connect_errors = 100000 lower_case_table_names =1 server_id=1 gtid_mode=on enforce_gtid_consistency=on #binlog log_bin=master-binlog log-slave-updates=1 binlog_format=row #relay log skip-slave-start=1
[root@www ~]# mysql -uroot -ptest mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.32 MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> grant replication slave on *.* to 'repl'@'192.168.174.%' identified by '123456'; Query OK, 0 rows affected, 1 warning (0.00 sec)
在主服务器上备份全库
[root@www tmp]# mysqldump -uroot -ptest --master-data=2 --single-transaction -A > /tmp/mysqlbak_`date +%Y%m%d`.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@www tmp]# service mysql restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!
从库上安装mysql（同上安装，略）
导入mysql主备份的mysql到从服务器上
[root@test ~]# mysql -uroot -ptest mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.32 MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> source /root/mysqlbak_20210616.sql;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| wordpress |
+--------------------+
5 rows in set (0.00 sec)
修改从服务器上的my.cnf
[root@test ~]# vi /etc/my.cnf [client] port = 3306 socket = /tmp/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql basedir = /usr/local/mysql datadir = /data/mysql/data port = 3306 socket = /tmp/mysql.sock log-error = error.log slow_query_log_file = slow.log character-set-server = utf8 open_files_limit = 65535 max_connections = 100 max_connect_errors = 100000 lower_case_table_names =1 server_id=2 gtid_mode=on enforce_gtid_consistency=on #binlog log_bin=master-binlog log-slave-updates=1 binlog_format=row #relay log skip-slave-start=1
重启从服务器上的mysql
[root@test ~]# service mysql restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!
查看gtid模式是否开启
mysql> show variables like '%gtid%'; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_next | AUTOMATIC | | gtid_owned | | | gtid_purged | | | session_track_gtids | OFF | +----------------------------------+-----------+ 8 rows in set (0.01 sec)
配置从服务器上的连接
[root@test ~]# mysql -uroot -ptest mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.32-log MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.174.62',（主IP） -> MASTER_USER='repl', -> MASTER_PASSWORD='123456', -> MASTER_PORT=3306, -> MASTER_AUTO_POSITION=1, -> MASTER_CONNECT_RETRY=10; Query OK, 0 rows affected, 2 warnings (0.01 sec)
启动slave，如果Slave_IO_Running与Slave_SQL_Running都是yes就代表从服务器配置成功。
mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.174.62 Master_User: repl Master_Port: 3306 Connect_Retry: 10 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: test-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: Yes（No 克隆uuid一样，修改/data/mysql/data/auto.cnf中uuid即可） Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 0 Relay_Log_Space: 154 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 1593 Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_UUID: Master_Info_File: /data/mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: 210616 16:49:14 Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified
3. 验证主从
在主服务器上新建一个数据库
[root@www tmp]# mysql -uroot -ptest mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.32-log MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.02 sec) mysql> use test; Database changed mysql> create table cool(id int,name varchar(10)); Query OK, 0 rows affected (0.07 sec) mysql> insert into cool(id,name) values(1,'xiaoming'); Query OK, 1 row affected (0.00 sec) mysql> select * from cool; +------+----------+ | id | name | +------+----------+ | 1 | xiaoming | +------+----------+ 1 row in set (0.00 sec)
去从库上查看是否有同步过去
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | | wordpress | +--------------------+ 6 rows in set (0.00 sec)
mysql> use test; Database changed mysql> select * from cool; +------+----------+ | id | name | +------+----------+ | 1 | xiaoming | +------+----------+ 1 row in set (0.00 sec)
4. GTID同步故障处理
1、主库新增记录，从库提示主键冲突
模拟故障场景：
主库：
mysql> use test; Database changed mysql> create table t_key(id int not null primary key,c1 varchar(20)); Query OK, 0 rows affected (0.00 sec) mysql> insert into t_key(id,c1) values('1','a'); Query OK, 1 row affected (0.00 sec) mysql> set sql_log_bin=0; Query OK, 0 rows affected (0.00 sec) mysql> delete from t_key; Query OK, 1 row affected (0.00 sec) mysql> set sql_log_bin=1; Query OK, 0 rows affected (0.00 sec)
从库：
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.174.62 Master_User: repl Master_Port: 3306 Connect_Retry: 10 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: test-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 0 Relay_Log_Space: 154 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 1593 Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_UUID: Master_Info_File: /data/mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: 210616 16:49:14 Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified
解决方法：
登录到从库，删除重复数据
mysql> stop slave; Query OK, 0 rows affected (0.00 sec)
mysql> delete from t_key where id=1; Query OK, 1 row affected (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.137.18 Master_User: repl Master_Port: 3306 Connect_Retry: 10 Master_Log_File: master-binlog.000001 Read_Master_Log_Pos: 2404 Relay_Log_File: test-relay-bin.000003 Relay_Log_Pos: 466 Relay_Master_Log_File: master-binlog.000001 Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 2404 Relay_Log_Space: 3143 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 18d11475-b5fd-11eb-9b94-000c29c40ee0 Master_Info_File: /data/mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 18d11475-b5fd-11eb-9b94-000c29c40ee0:1-11 Executed_Gtid_Set: 18d11475-b5fd-11eb-9b94-000c29c40ee0:1-11, 79c2fe65-b621-11eb-a65a-000c29d77752:1-4 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t_key | +----------------+ 1 row in set (0.00 sec)
任务3 全备+binlog恢复
使用mysqldump备份所有数据库，并恢复。要求保持数据库的一致性的前提下尽量减少数据库的锁,然后用binlog前滚到指定时间点。
周一到周日，每天晚上22:00都备份了一个全备，某一天（周四）早上10:00数据库down了，怎么恢复到最新的时间点。
思路：周三晚上22:00通过全备恢复，周三晚上22:00-周四早上10:00通过binlog恢复
准备两台mysql服务器，不要是主从服务器。
1. 准备数据
mysql> create database cool; Query OK, 1 row affected (0.00 sec) mysql> use cool; Database changed mysql> create table cool(id int,name varchar(10)); Query OK, 0 rows affected (0.00 sec) mysql> insert into cool values(1,'a'),(2,'b'),(3,'c'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> commit -> ; Query OK, 0 rows affected (0.00 sec)
2. 备份数据库
假设周三晚上10:00进行了一个全备，如下：
[root@www ~]# mysqldump -uroot -ptest --set-gtid-purged=OFF --master-data=2 --single-transaction -A > /opt/full_$(date +%F).sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@www ~]# cd /opt/ [root@www opt]# ls full_2021-06-16.sql [root@www opt]#
3. 新建一个数据库及表
周三晚上十点到周四早上十点模拟新增数据。
mysql> create database t1; Query OK, 1 row affected (0.00 sec) mysql> use t1; Database changed mysql> create table t1(id int,name varchar(20)); Query OK, 0 rows affected (0.01 sec) mysql> insert into t1(id,name) values(1,'php'),(2,'java'),(3,'python'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t1; +------+--------+ | id | name | +------+--------+ | 1 | php | | 2 | java | | 3 | python | +------+--------+ 3 rows in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec)
4. 模拟故障删库
周四早上十点模拟故障删库t1
mysql> drop database t1; Query OK, 1 row affected (0.00 sec)
5. 查找binlog的起点及终点
在故障的mysql服务器上查看周三晚上十点备份的binlog起点。
[root@www opt]# vi full_2021-06-16.sql -- MySQL dump 10.13 Distrib 5.7.32, for linux-glibc2.12 (x86_64) -- -- Host: localhost Database: -- ------------------------------------------------------ -- Server version 5.7.32-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='master-binlog.000003', MASTER_LOG_POS=1994; -- -- Current Database: `cool` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `cool` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `cool`;
查看binlog终点
mysql> show master status; +----------------------+----------+--------------+------------------+-------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------------+----------+--------------+------------------+-------------------------------------------+ | master-binlog.000003 | 2751 | | | e9355dde-caaa-11eb-875f-000c29554ded:1-13 | +----------------------+----------+--------------+------------------+-------------------------------------------+ 1 row in set (0.00 sec)
mysql> show master status; +----------------------+----------+--------------+------------------+-------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------------+----------+--------------+------------------+-------------------------------------------+ | master-binlog.000003 | 2751 | | | e9355dde-caaa-11eb-875f-000c29554ded:1-13 | +----------------------+----------+--------------+------------------+-------------------------------------------+ 1 row in set (0.00 sec) mysql> show binlog events in 'master-binlog.000003'; +----------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +----------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------+ | master-binlog.000003 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.32-log, Binlog ver: 4 | | master-binlog.000003 | 123 | Previous_gtids | 1 | 154 | | | master-binlog.000003 | 154 | Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'e9355dde-caaa-11eb-875f-000c29554ded:1' | | master-binlog.000003 | 219 | Query | 1 | 313 | create database test | | master-binlog.000003 | 313 | Gtid | 1 | 378 | SET @@SESSION.GTID_NEXT= 'e9355dde-caaa-11eb-875f-000c29554ded:2' | | master-binlog.000003 | 378 | Query | 1 | 494 | use `test`; create table cool(id int,name varchar(10)) | | master-binlog.000003 | 494 | Gtid | 1 | 559 | SET @@SESSION.GTID_NEXT= 'e9355dde-caaa-11eb-875f-000c29554ded:3' | | master-binlog.000003 | 559 | Query | 1 | 631 | BEGIN | | master-binlog.000003 | 631 | Table_map | 1 | 681 | table_id: 108 (test.cool) | | master-binlog.000003 | 681 | Write_rows | 1 | 730 | table_id: 108 flags: STMT_END_F | | master-binlog.000003 | 730 | Xid | 1 | 761 | COMMIT /* xid=10 */ | | master-binlog.000003 | 761 | Gtid | 1 | 826 | SET @@SESSION.GTID_NEXT= 'e9355dde-caaa-11eb-875f-000c29554ded:4' | | master-binlog.000003 | 826 | Query | 1 | 962 | use `test`; create table t_key(id int not null primary key,c1 varchar(20)) | | master-binlog.000003 | 962 | Gtid | 1 | 1027 | SET @@SESSION.GTID_NEXT= 'e9355dde-caaa-11eb-875f-000c29554ded:5' | | master-binlog.000003 | 1027 | Query | 1 | 1099 | BEGIN | | master-binlog.000003 | 1099 | Table_map | 1 | 1150 | table_id: 109 (test.t_key) | | master-binlog.000003 | 1150 | Write_rows | 1 | 1192 | table_id: 109 flags: STMT_END_F | | master-binlog.000003 | 1192 | Xid | 1 | 1223 | COMMIT /* xid=18 */ | | master-binlog.000003 | 1223 | Gtid | 1 | 1288 | SET @@SESSION.GTID_NEXT= 'e9355dde-caaa-11eb-875f-000c29554ded:6' | | master-binlog.000003 | 1288 | Query | 1 | 1380 | drop database test | | master-binlog.000003 | 1380 | Gtid | 1 | 1445 | SET @@SESSION.GTID_NEXT= 'e9355dde-caaa-11eb-875f-000c29554ded:7' | | master-binlog.000003 | 1445 | Query | 1 | 1539 | create database cool | | master-binlog.000003 | 1539 | Gtid | 1 | 1604 | SET @@SESSION.GTID_NEXT= 'e9355dde-caaa-11eb-875f-000c29554ded:8' | | master-binlog.000003 | 1604 | Query | 1 | 1720 | use `cool`; create table cool(id int,name varchar(10)) | | master-binlog.000003 | 1720 | Gtid | 1 | 1785 | SET @@SESSION.GTID_NEXT= 'e9355dde-caaa-11eb-875f-000c29554ded:9' | | master-binlog.000003 | 1785 | Query | 1 | 1857 | BEGIN | | master-binlog.000003 | 1857 | Table_map | 1 | 1907 | table_id: 111 (cool.cool) | | master-binlog.000003 | 1907 | Write_rows | 1 | 1963 | table_id: 111 flags: STMT_END_F | | master-binlog.000003 | 1963 | Xid | 1 | 1994 | COMMIT /* xid=33 */ | | master-binlog.000003 | 1994 | Gtid | 1 | 2059 | SET @@SESSION.GTID_NEXT= 'e9355dde-caaa-11eb-875f-000c29554ded:10' | | master-binlog.000003 | 2059 | Query | 1 | 2147 | create database t1 | | master-binlog.000003 | 2147 | Gtid | 1 | 2212 | SET @@SESSION.GTID_NEXT= 'e9355dde-caaa-11eb-875f-000c29554ded:11' | | master-binlog.000003 | 2212 | Query | 1 | 2322 | use `t1`; create table t1(id int,name varchar(20)) | | master-binlog.000003 | 2322 | Gtid | 1 | 2387 | SET @@SESSION.GTID_NEXT= 'e9355dde-caaa-11eb-875f-000c29554ded:12' | | master-binlog.000003 | 2387 | Query | 1 | 2457 | BEGIN | | master-binlog.000003 | 2457 | Table_map | 1 | 2503 | table_id: 145 (t1.t1) | | master-binlog.000003 | 2503 | Write_rows | 1 | 2569 | table_id: 145 flags: STMT_END_F | | master-binlog.000003 | 2569 | Xid | 1 | 2600 | COMMIT /* xid=500 */ | | master-binlog.000003 | 2600 | Gtid | 1 | 2665 | SET @@SESSION.GTID_NEXT= 'e9355dde-caaa-11eb-875f-000c29554ded:13' | | master-binlog.000003 | 2665 | Query | 1 | 2751 | drop database t1 | +----------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------+ 40 rows in set (0.00 sec)
所以可以知道，binlog的起点是1994，终点为2665
6. 截取binlog起点及终点的日志
[root@www opt]# mysqlbinlog --skip-gtids --start-position=1994 --start-position=2665 /data/mysql/data/master-binlog.000003 > /opt/binlog.sql
[root@www opt]# ls
binlog.sql full_2021-06-16.sql
7. 临时库上恢复数据库到最新时间点
临时库上恢复全备+binlog，将全备文件及截取的binlog文件恢复到临时库上（另外一台临时服务器上）
[root@www opt]# scp /opt/full_2021-06-16.sql root@192.168.174.62:/opt/ root@192.168.174.62's password: full_2021-06-16.sql 100% 854KB 853.6KB/s 00:00 [root@www opt]# scp /opt/binlog.sql root@192.168.174.62:/opt/ root@192.168.174.62's password: binlog.sql 100% 1393 1.4KB/s 00:00
[root@test opt]# mysql -uroot -ptest
mysql> source /opt/full_2021-06-16.sql;
#现在只是恢复到周三晚上十点。所以并没有周三晚上十点到周四早上十点的数据。即t1数据库。
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | cool | | mysql | | performance_schema | | sys | | wordpress | +--------------------+ 6 rows in set (0.00 sec)
mysql> source /opt/binlog.sql; #恢复周三晚上十点到周四早上十点新增的数据。
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | cool | | mysql | | performance_schema | | sys | | t1 | | wordpress | +--------------------+ 8 rows in set (0.00 sec)
mysql> use t1; Database changed mysql> select * from t1; +------+--------+ | id | name | +------+--------+ | 1 | php | | 2 | java | | 3 | python | +------+--------+ 3 rows in set (0.00 sec)
任务4 xtrabackup备份恢复
https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/
1. 安装xtrabackup
[root@test ~]# wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo --2021-06-16 19:22:06-- http://mirrors.aliyun.com/repo/epel-7.repo Resolving mirrors.aliyun.com (mirrors.aliyun.com)... 36.248.215.114, 36.248.215.120, 36.248.215.118, ... Connecting to mirrors.aliyun.com (mirrors.aliyun.com)|36.248.215.114|:80... connected. HTTP request sent, awaiting response... 200 OK Length: 664 [application/octet-stream] Saving to: ‘/etc/yum.repos.d/epel.repo’ 100%[========================>] 664 --.-K/s in 0s 2021-06-16 19:22:07 (40.7 MB/s) - ‘/etc/yum.repos.d/epel.repo’ saved [664/664]
[root@test ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
[root@test ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.22/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm --2021-06-16 19:24:44-- https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.22/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm Resolving downloads.percona.com (downloads.percona.com)... 162.220.4.222, 162.220.4.221, 74.121.199.231 Connecting to downloads.percona.com (downloads.percona.com)|162.220.4.222|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 7992904 (7.6M) [application/octet-stream] Saving to: ‘percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm’ 100%[========================>] 7,992,904 2.63MB/s in 2.9s 2021-06-16 19:24:49 (2.63 MB/s) - ‘percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm’ saved [7992904/7992904]
[root@test ~]# yum -y install percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm
2. xtrabackup全备
[root@test ~]# innobackupex --user=root --password=test --no-timestamp /opt/full
[root@test ~]# ll /opt/full total 77880 -rw-r-----. 1 root root 487 Jun 16 19:46 backup-my.cnf drwxr-x---. 2 root root 49 Jun 16 19:46 cool -rw-r-----. 1 root root 820 Jun 16 19:46 ib_buffer_pool -rw-r-----. 1 root root 79691776 Jun 16 19:46 ibdata1 drwxr-x---. 2 root root 4096 Jun 16 19:46 mysql drwxr-x---. 2 root root 8192 Jun 16 19:46 performance_schema drwxr-x---. 2 root root 8192 Jun 16 19:46 sys drwxr-x---. 2 root root 4096 Jun 16 19:46 wordpress -rw-r-----. 1 root root 68 Jun 16 19:46 xtrabackup_binlog_info -rw-r-----. 1 root root 135 Jun 16 19:46 xtrabackup_checkpoints -rw-r-----. 1 root root 556 Jun 16 19:46 xtrabackup_info -rw-r-----. 1 root root 2560 Jun 16 19:46 xtrabackup_logfile
3. 模拟数据库故障
[root@test ~]# pkill mysqld [root@test ~]# ls /data/mysql/data/ auto.cnf ib_logfile1 private_key.pem ca-key.pem localhost.localdomain.pid public_key.pem ca.pem master-binlog.000001 relay-log.info client-cert.pem master-binlog.000002 server-cert.pem client-key.pem master-binlog.000003 server-key.pem cool master-binlog.000004 sys error.log master-binlog.index test-relay-bin.000001 ib_buffer_pool master.info test-relay-bin.000002 ibdata1 mysql test-relay-bin.index ib_logfile0 performance_schema wordpress [root@test ~]# rm -rf /data/mysql/data/* [root@test ~]# ls /data/mysql/data/ [root@test ~]#
4. 恢复完全备份的数据
[root@test ~]# innobackupex --apply-log /opt/full/
5. 拷贝数据到/data/mysql/data下及修改权限
[root@test ~]# cp -a /opt/full/* /data/mysql/data/ [root@test ~]# chown -R mysql.mysql /data/mysql/data/*
6. 启动数据库查看数据
[root@test ~]# service mysql start Starting MySQL.Logging to '/data/mysql/data/error.log'. . SUCCESS!
[root@test ~]# mysql -uroot -ptest mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.32-log MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | cool | | mysql | | performance_schema | | sys | | wordpress | +--------------------+ 6 rows in set (0.00 sec)
