linux 学习笔记-060-Mysql-Mysql 主从相关知识(1)

发布于 2018-04-03  394 次阅读


主从不一致,该怎么处理?

这里说下,一般情况下的修复主从

在从服务器上操作:

删除 am 库,正常流程,对库的操作应该是要在主服务器上做的,在从服务器上做会导致主从不一致

mysql> drop database am;

Query OK, 8 rows affected (0.42 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| mysql2             |

| performance_schema |

| test               |

| zrlog              |

+--------------------+

6 rows in set (0.00 sec)

在主服务器上操作:

修改 am 库

mysql> use am;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+--------------+

| Tables_in_am |

+--------------+

| comment      |

| link         |

| log          |

| lognav       |

| plugin       |

| type         |

| user         |

| website      |

+--------------+

8 rows in set (0.00 sec)

mysql> drop table am.plugin;

Query OK, 0 rows affected (0.05 sec)

mysql> show tables;

+--------------+

| Tables_in_am |

+--------------+

| comment      |

| link         |

| log          |

| lognav       |

| type         |

| user         |

| website      |

+--------------+

7 rows in set (0.00 sec)

在从服务器上查看主从状态,提示"Slave_SQL_Running: No",并且有错误提示

mysql> show slave status\G

             Slave_IO_Running: Yes

            Slave_SQL_Running: No

               Last_SQL_Errno: 1051

               Last_SQL_Error: Error 'Unknown table 'am.plugin'' on query. Default database: 'am'. Query: 'DROP TABLE `plugin` /* generated by server */'

尝试修复主从,使主从保持一致

从服务器上,先"stop slave;",然后"set global sql_slave_skip_counter=1;",最后"start slave;",看能否修复,不能的话,往下继续

mysql> stop slave;

Query OK, 0 rows affected (0.03 sec)

msyql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

mysql> start slave;

Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G

             Slave_IO_Running: Yes

            Slave_SQL_Running: No

                   Last_Errno: 1051

                   Last_Error: Error 'Unknown table 'am.plugin'' on query. Default database: 'am'. Query: 'DROP TABLE `plugin` /* generated by server */'

在主服务器操作,先锁表,再次查询 File 和 Position 的值,然后把 am 库备份出来,因为刚是把从服务器的 am 库删除导致的不一致,所以要到从服务器上把 am 库还原

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.06 sec)

mysql> show master status;

+-------------+----------+--------------+------------------+-------------------+

| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-------------+----------+--------------+------------------+-------------------+

| am01.000001 |    10494 |              |                  |                   |

+-------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysql> quit

Bye

[root@am-01:~#] mysqldump -uroot -pitsupport.0 am > /tmp/am.sql

Warning: Using a password on the command line interface can be insecure.

[root@am-01:~#] ls -l /tmp/am.sql

-rw-r--r-- 1 root root 8202 4 月   3 23:22 /tmp/am.sql

在从服务器上操作

先停止主从,然后重新从主服务器拉取 am 库的备份,新建 am 库并还原

mysql> stop slave;

Query OK, 0 rows affected (0.03 sec)

mysql> quit

Bye

[root@am-02:~#] cd /tmp/

[root@am-02:/tmp#] scp 172.17.1.240:/tmp/am.sql /tmp/

root@172.17.1.240's password:

am.sql                                                                                               100% 8202     8.0KB/s   00:00   

[root@am-02:/tmp#] mysql -uroot

mysql> create database am;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| am                 |

| mysql              |

| mysql2             |

| performance_schema |

| test               |

| zrlog              |

+--------------------+

7 rows in set (0.00 sec)

mysql> quit

Bye

[root@am-02:/tmp#] mysql -uroot am < /tmp/am.sql

[root@am-02:/tmp#] mysql -uroot

mysql> use am;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+--------------+

| Tables_in_am |

+--------------+

| comment      |

| link         |

| log          |

| lognav       |

| type         |

| user         |

| website      |

+--------------+

7 rows in set (0.00 sec)

在从服务器上,为保险,再次运行"stop slave;"停止同步,然后运行"reset slave;"复位同步,再运行同步命令"change master",最后再开启同步"start slave;",测试可以见到,同步没问题了

mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> reset slave;

Query OK, 0 rows affected (0.07 sec)

mysql> change master to master_host='172.17.1.240',master_user='am',master_password='itsupport.0',master_log_file='am01.000001',master_log_pos=10494;

Query OK, 0 rows affected, 2 warnings (0.13 sec)

mysql> start slave;

Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

                   Last_Errno: 0

                   Last_Error:

主服务器上,记得把表解锁

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

mysql> quit

Bye

Xtrabackup 在线对 MySQL 做主从复制

mysqldump 对于导出 10G 以下的数据库或几个表,速度尚可,但是如果数据量达到 100G 以上,使用 mysqldump 做数据库备份,这无论对服务器还是库都会产生很大的问题

Xtrabackup 的优点:

01:支持在线热备份

02:全量和增量备份 MySQL

03:自动完成备份鉴定

04:节约磁盘空间和网络带宽

05:速度快,节省时间

06:比较容易创建主从同步

这里使用 Xtrabackup 做全量备份和全量恢复的操作

主服务器:

首先创建一个用来做主从的用户 backup,并赋予其合适权限

[root@am-01:~#] mysql -uroot -pitsupport.0

mysql> grant replication slave on *.* to 'am'@'172.17.1.%' identified by 'itsupport.0';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> quit

Bye

下载 Xtrabackup 并安装,之后把 Xtrabackup 加入到系统变量中,以便能使用 innobackupex 命令

[root@am-01:~#] cd /usr/local/src/

[root@am-01:/usr/local/src#] wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.2/binary/tarball/percona-xtrabackup-2.3.2-Linux-x86_64.tar.gz

--2018-04-04 01:11:02--  https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.2/binary/tarball/percona-xtrabackup-2.3.2-Linux-x86_64.tar.gz

正在解析主机 www.percona.com (www.percona.com)... 74.121.199.234

正在连接 www.percona.com (www.percona.com)|74.121.199.234|:443... 已连接。

已发出 HTTP 请求,正在等待回应... 200 OK

长度:26753461 (26M) [application/x-gzip]

正在保存至: “percona-xtrabackup-2.3.2-Linux-x86_64.tar.gz”

100%[==============================================================================================>] 26,753,461   458KB/s 用时 42s   

2018-04-04 01:11:54 (621 KB/s) - 已保存 “percona-xtrabackup-2.3.2-Linux-x86_64.tar.gz” [26753461/26753461])

[root@am-01:/usr/local/src#] tar xvf percona-xtrabackup-2.3.2-Linux-x86_64.tar.gz

[root@am-01:/usr/local/src#] mv percona-xtrabackup-2.3.2-Linux-x86_64 xtrabackup

[root@am-01:/usr/local/src#] cd xtrabackup/bin/

[root@am-01:/usr/local/src/xtrabackup/bin#] pwd

/usr/local/src/xtrabackup/bin

[root@am-01:/usr/local/src/xtrabackup/bin#] vim /etc/profile

  export PATH=$PATH:/usr/local/src/xtrabackup/bin

[root@am-01:/usr/local/src/xtrabackup/bin#] source /etc/profile

使用 innobackupex 命令做全量备份

假设 MySQL 安装目录在/usr/local/mysql/,my.cnf 配置文件/etc/my.cnf,端口 3306,数据目录/data/mysql/,sock 位于/tmp/mysql.sock,备份数据放在/tmp/backup/mysql(这里做实验时有些参数没写)

备份出来的文件默认会以当天日期+时间戳命名备份,一般会对它进行 tar 压缩,由于 tar 只能单进程,所以往往这个压缩过程会比备份过程耗时 2 倍还多

重点信息输出:filename 'am01.000001', position '11891'

[root@am-01:~#] innobackupex --defaults-file=/etc/my.cnf --host='172.17.1.240' --port=3306 --user=backup --password='itsupport.0' /tmp/backup/mysql/

[root@am-01:~#] ls /tmp/backup/mysql/

2018-04-04_06-13-56

从服务器:

跟主服务器一样,安装 Xtrabackup 并把其加入到系统变量中

[root@am-02:~#] cd /usr/local/src/

[root@am-02:/usr/local/src#] wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.2/binary/tarball/percona-xtrabackup-2.3.2-Linux-x86_64.tar.gz

--2018-04-04 01:27:26--  https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.2/binary/tarball/percona-xtrabackup-2.3.2-Linux-x86_64.tar.gz

正在解析主机 www.percona.com (www.percona.com)... 74.121.199.234

正在连接 www.percona.com (www.percona.com)|74.121.199.234|:443... 已连接。

已发出 HTTP 请求,正在等待回应... 200 OK

长度:26753461 (26M) [application/x-gzip]

正在保存至: “percona-xtrabackup-2.3.2-Linux-x86_64.tar.gz”

100%[==============================================================================================>] 26,753,461   144KB/s 用时 83s   

2018-04-04 01:28:55 (314 KB/s) - 已保存 “percona-xtrabackup-2.3.2-Linux-x86_64.tar.gz” [26753461/26753461])

[root@am-02:/usr/local/src#] tar xvf percona-xtrabackup-2.3.2-Linux-x86_64.tar.gz

[root@am-02:/usr/local/src#] mv percona-xtrabackup-2.3.2-Linux-x86_64 xtrabackup

[root@am-02:/usr/local/src#] vim /etc/profile

  export PATH=$PATH:/usr/local/src/xtrabackup/bin

[root@am-02:/tmp#] source /etc/profile

把主服务器的全量备份文件拉取到从服务器

[root@am-02:/usr/local/src#] scp -r 172.17.1.240:/tmp/backup/mysql/* /tmp/

[root@am-02:/usr/local/src#] ls -l /tmp/ | grep 2018

drwx------ 8 root root 4096 4 月 4 06:18 2018-04-04_06-13-56

指定使用多少内存做数据恢复以及备份文件名称,然后切记需要把从服务器 Mysql 停止运行并清空 datadir 目录下的所有文件,最后使用 my.cnf 文件,将完整的 mysql 数据文件拷贝到 datadir 下

[root@am-02:/tmp#] innobackupex --use-memory=2G --apply-log 2018-04-04_06-13-56

[root@am-02:/tmp#] service mysqld stop

Shutting down MySQL.. SUCCESS!

[root@am-02:/tmp#] ps -aux | grep mysql

root       2999  0.0  0.0 112672   980 pts/0    S+   01:47   0:00 grep --color=auto mysql

[root@am-02:/tmp#] rm -rf /data/mysql/*

[root@am-02:/tmp#] ls -l /data/mysql/

总用量 0

[root@am-02:/tmp#] innobackupex --defaults-file=/etc/my.cnf --use-memory=2G --copy-back 2018-04-04_06-13-56

做主从:

设置 datadir 目录正确的所有者和所属组

[root@am-02:/tmp#] chown -R mysql:mysql /data/mysql/

[root@am-02:/tmp#] ls -l /data/mysql/

总用量 176156

drwx------ 2 mysql mysql     4096 4 月   4 01:51 am

-rw-r----- 1 mysql mysql 79691776 4 月   4 01:51 ibdata1

-rw-r----- 1 mysql mysql 50331648 4 月   4 01:51 ib_logfile0

-rw-r----- 1 mysql mysql 50331648 4 月   4 01:51 ib_logfile1

drwx------ 2 mysql mysql     4096 4 月   4 01:51 mysql

drwx------ 2 mysql mysql     4096 4 月   4 01:51 mysql2

drwx------ 2 mysql mysql     4096 4 月   4 01:51 performance_schema

drwx------ 2 mysql mysql       19 4 月   4 01:51 test

-rw-r----- 1 mysql mysql       17 4 月   4 01:51 xtrabackup_binlog_pos_innodb

-rw-r----- 1 mysql mysql      532 4 月   4 01:51 xtrabackup_info

drwx------ 2 mysql mysql     4096 4 月   4 01:51 zrlog

启动从库

[root@am-02:/tmp#] service mysqld start

Starting MySQL.Logging to '/data/mysql/am-02.err'.

.. SUCCESS!

[root@am-02:/tmp#] ps -aux | grep mysql

root       3081  0.5  0.1  11776  1560 pts/0    S    01:54   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/am-02.pid

mysql      3242 33.2 45.2 1300824 453880 pts/0  Sl   01:54   0:02 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/am-02.err --pid-file=/data/mysql/am-02.pid --socket=/tmp/mysql.sock

root       3270  0.0  0.0 112672   980 pts/0    S+   01:54   0:00 grep --color=auto mysql

查询 filename 和 position 的值,配置从服务器,启动从服务器

[root@am-02:/tmp#] cat 2018-04-04_06-13-56/xtrabackup_binlog_info

am01.000001 11891

[root@am-02:/tmp#] mysql -uroot -pitsupport.0

mysql> change master to master_host='172.17.1.240', master_user='backup', master_password='itsupport.0', master_log_file='am01.000001', master_log_pos=11891;

Query OK, 0 rows affected, 2 warnings (0.15 sec)

mysql> show slave status\G

             Slave_IO_Running: No

            Slave_SQL_Running: No

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes