mysql1.2.17,17.1 MySQL主从介绍17.2 准备工作17.3 配置主17.4 配置从17.5 测试主从同步...

最新推荐文章于 2022-07-17 03:36:38 发布

不知盗

最新推荐文章于 2022-07-17 03:36:38 发布

阅读量134

点赞数

文章标签： mysql1.2.17

- 17.1 MySQL主从介绍

- 17.2 准备工作

- 17.3 配置主

- 17.4 配置从

- 17.5 测试主从同步

# 17.1 MySQL主从介绍

- MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步的

- MySQL主从是基于binlog的，主上须开启binlog才能进行主从。bilog，是二进制文件，无法cat

- 主从过程大致有3个步骤

1. 主将更改操作记录到binlog里

2. 从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里；relaylog，中继日志

3. 从根据relaylog里面的sql语句按顺序执行

- 主上有一个log dump线程，用来和从的I/O线程传递binlog

- 从上有两个线程，其中I/O线程用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的sql语句落地

- mysql原理图

195502071.png?p_w_picpathslim

- 使用场景：

- 一、数据备份，主机器宕机，从机器还能随时对web提供服务

- 二、数据备份且可以分担主机器被调用数据时的压力，mysql主从，是有方向性的，写数据，必须从主机器开始；如果不依照原理会导致数据紊乱

# 17.2 准备工作

- 主从配置，主上操作

- mysql安装总结

- 进入src专用的下载目录下，下载mysql二进制免编译包

- wget 5.6版本，x64位

- 解压

- tar zxvf 。。。

- 移动

- mv 。。 /usr/local/mysql

- 移动时，需注意这个目录是否已经存在，如果存在了，需要删除旧文件

- 初始化配置

- 进入所在的mysql目录

```

[root@aming-01 mysql]# pwd

/usr/local/mysql

./scripts/mysql_install_db –user=mysql –datadir=/data/mysql

–user=mysql 需提前创建

```

- 编辑my.cnf文件(7版本的centos系统，就自带安装一个mariadb，所以就会自动生成有一个my.cnf，就省去了拷贝)

```

vim /etc/my.cnf //添加以下两行内容

datadir=/data/mysql

socket=/tmp/mysql.sock

```

- 拷贝启动脚本 cp support-files/mysql.server /etc/init.d/mysqld

- 编辑启动脚本

vim /etc/init.d/mysqld //对以下两行进行指定路径

basedir=

datadir=

指定basedir的路径 /usr/local/mysql

指定datadir的路径 /data/mysql

- 最重要的一步，记得查看/data/mysql 的默认属主、属组，如果不是mysql的，启东时会因为无法写入数据而不能启动

- chomd mysql:mysql /data/mysql

- 然后就可以尝试启动

/etc/init.d/mysql start

- [x] 主机器

- 192.168.202.131

- mysql启动情况

```

[root@aming-01 ~]# ps aux |grep mysql

root 941 0.0 0.1 115392 1688 ? S 20:14 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/aming-01.pid

mysql 1347 1.3 45.3 1300816 452976 ? Sl 20:14 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/aming-01.err --pid-file=/data/mysql/aming-01.pid --socket=/tmp/mysql.sock

root 2353 0.0 0.0 112684 980 pts/0 S+ 20:17 0:00 grep --color=auto mysql

[root@aming-01 ~]#

```

- [x] 从机器

- 192.168.202.132

- mysql启动情况

```

[root@aming-02 ~]# ps aux |grep mysql

root 1074 0.0 0.1 115392 1680 ? S 20:15 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/aming-02.pid

mysql 1523 1.7 45.2 1300784 452076 ? Sl 20:15 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/aming-02.err --pid-file=/data/mysql/aming-02.pid --socket=/tmp/mysql.sock

root 2284 0.0 0.0 112680 980 pts/0 S+ 20:17 0:00 grep --color=auto mysql

[root@aming-02 ~]#

```

# 17.3 配置主

- 安装mysql

- 修改my.cnf，增加server-id=131和log_bin=aminglinux1

- 修改完配置文件后，启动或者重启mysqld服务

- 把mysql库备份并恢复成aming库，作为测试数据

- mysqldump -uroot mysql > /tmp/mysql.sql

- mysql -uroot -e “create database aming”

- mysql -uroot aming

- 创建用作同步数据的用户

- grant replication slave on *.* to 'repl'@slave_ip identified by 'password';

- flush tables with read lock;

- show master status;

- 修改主机器上的配置文件

```

[root@aming-01 ~]# vi /etc/my.cnf

[mysqld]

datadir=/data/mysql

socket=/tmp/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Settings user and group are ignored when systemd is used.

# If you need to run mysqld under a different user or group,

# customize your systemd unit file for mariadb according to the

# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]

#log-error=/var/log/mariadb/mariadb.log

#pid-file=/var/run/mariadb/mariadb.pid

# include all files from the config directory

#!includedir /etc/my.cnf.d

"/etc/my.cnf" 19L, 560C

```

- 更改为

```

[root@aming-01 ~]# vi /etc/my.cnf

[mysqld]

datadir=/data/mysql

socket=/tmp/mysql.sock

server-id=131

log_bin=aminglinux1

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Settings user and group are ignored when systemd is used.

# If you need to run mysqld under a different user or group,

# customize your systemd unit file for mariadb according to the

# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]

#log-error=/var/log/mariadb/mariadb.log

#pid-file=/var/run/mariadb/mariadb.pid

# include all files from the config directory

#!includedir /etc/my.cnf.d

:wq

```

- 修改之后要重启mysql,进入/data/mysql/目录下

```

[root@aming-01 ~]# /etc/init.d/mysqld restart

Shutting down MySQL.. SUCCESS!

Starting MySQL.. SUCCESS!

[root@aming-01 ~]#

[root@aming-01 ~]# pwd

/root

[root@aming-01 ~]# cd /data/mysql

[root@aming-01 mysql]# ls -lt

总用量 110768

-rw-rw----. 1 mysql mysql 50331648 11月 7 21:44 ib_logfile0

-rw-rw----. 1 mysql mysql 12582912 11月 7 21:44 ibdata1

-rw-rw----. 1 mysql mysql 29700 11月 7 21:43 aming-01.err

-rw-rw----. 1 mysql mysql 5 11月 7 21:43 aming-01.pid

-rw-rw----. 1 mysql mysql 21 11月 7 21:43 aminglinux1.index

-rw-rw----. 1 mysql mysql 120 11月 7 21:43 aminglinux1.000001

drwx------. 2 mysql mysql 4096 11月 6 22:12 zrlog

-rw-rw----. 1 mysql mysql 44847 10月 31 22:02 localhost.err

drwx------. 2 mysql mysql 4096 10月 30 23:13 mysql2

-rw-rw----. 1 mysql mysql 15630 10月 25 23:10 www.qq123.com.err

-rw-rw----. 1 mysql mysql 49405 10月 24 23:57 localhost.localdomain.err

-rw-rw----. 1 mysql mysql 56 10月 14 20:44 auto.cnf

drwx------. 2 mysql mysql 4096 10月 14 20:31 mysql

drwx------. 2 mysql mysql 4096 10月 14 20:31 performance_schema

-rw-rw----. 1 mysql mysql 50331648 10月 14 20:31 ib_logfile1

drwx------. 2 mysql mysql 6 10月 14 20:31 test

[root@aming-01 mysql]#

```

- .index 索引页，这个文件是必须要有的

- .000001 这个是二进制日志文件，会持续生成2、3、4等等(这个文件是实现主从配置的根本，没有这个文件根本没有办法实现)

- 第二步，准备一个数据，做演示用的，重新来搞一个数据出来，在blog基础上备份一个出来

```

[root@aming-01 mysql]# ls -lt

总用量 110768

-rw-rw----. 1 mysql mysql 50331648 11月 7 21:44 ib_logfile0

-rw-rw----. 1 mysql mysql 12582912 11月 7 21:44 ibdata1

-rw-rw----. 1 mysql mysql 29700 11月 7 21:43 aming-01.err

-rw-rw----. 1 mysql mysql 5 11月 7 21:43 aming-01.pid

-rw-rw----. 1 mysql mysql 21 11月 7 21:43 aminglinux1.index

-rw-rw----. 1 mysql mysql 120 11月 7 21:43 aminglinux1.000001

drwx------. 2 mysql mysql 4096 11月 6 22:12 zrlog

-rw-rw----. 1 mysql mysql 44847 10月 31 22:02 localhost.err

drwx------. 2 mysql mysql 4096 10月 30 23:13 mysql2

-rw-rw----. 1 mysql mysql 15630 10月 25 23:10 www.qq123.com.err

-rw-rw----. 1 mysql mysql 49405 10月 24 23:57 localhost.localdomain.err

-rw-rw----. 1 mysql mysql 56 10月 14 20:44 auto.cnf

drwx------. 2 mysql mysql 4096 10月 14 20:31 mysql

drwx------. 2 mysql mysql 4096 10月 14 20:31 performance_schema

-rw-rw----. 1 mysql mysql 50331648 10月 14 20:31 ib_logfile1

drwx------. 2 mysql mysql 6 10月 14 20:31 test

[root@aming-01 mysql]# mysqldump -uroot -paminglinux zrlog > /tmp/zrlog.sql

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

[root@aming-01 mysql]#

[root@aming-01 mysql]# du -sh /tmp/zrlog.sql

12K/tmp/zrlog.sql

[root@aming-01 mysql]#

```

- 新创建一个库,叫aming的库

```

[root@aming-01 mysql]# mysql -uroot -paminglinux -e "create database aming"

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

[root@aming-01 mysql]#

```

- 创建库之后还需要把这个数据恢复一下

```

[root@aming-01 mysql]# mysql -uroot -paminglinux aming

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

[root@aming-01 mysql]#

```

- 现在要做的主从，参考的对象就是这个库 aming这个库

```

[root@aming-01 mysql]# ls -lt

总用量 225468

-rw-rw----. 1 mysql mysql 50331648 11月 7 21:56 ib_logfile0

-rw-rw----. 1 mysql mysql 79691776 11月 7 21:56 ibdata1

-rw-rw----. 1 mysql mysql 10543 11月 7 21:56 aminglinux1.000001

drwx------. 2 mysql mysql 4096 11月 7 21:56 aming

-rw-rw----. 1 mysql mysql 29700 11月 7 21:43 aming-01.err

-rw-rw----. 1 mysql mysql 5 11月 7 21:43 aming-01.pid

-rw-rw----. 1 mysql mysql 21 11月 7 21:43 aminglinux1.index

drwx------. 2 mysql mysql 4096 11月 6 22:12 zrlog

-rw-rw----. 1 mysql mysql 44847 10月 31 22:02 localhost.err

drwx------. 2 mysql mysql 4096 10月 30 23:13 mysql2

-rw-rw----. 1 mysql mysql 15630 10月 25 23:10 www.qq123.com.err

-rw-rw----. 1 mysql mysql 49405 10月 24 23:57 localhost.localdomain.err

-rw-rw----. 1 mysql mysql 56 10月 14 20:44 auto.cnf

drwx------. 2 mysql mysql 4096 10月 14 20:31 mysql

drwx------. 2 mysql mysql 4096 10月 14 20:31 performance_schema

-rw-rw----. 1 mysql mysql 50331648 10月 14 20:31 ib_logfile1

drwx------. 2 mysql mysql 6 10月 14 20:31 test

[root@aming-01 mysql]#

```

- 先进入到mysql里面来

```

[root@aming-01 mysql]# mysql -uroot -paminglinux

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 6

Server version: 5.6.36-log MySQL Community Server (GPL)

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>

```

- 创建用作同步数据的用户

```

mysql> grant replication slave on *.* to 'repl'@'192.168.202.132' identified by 'aminglinux111';

Query OK, 0 rows affected (0.00 sec)

mysql>

```

- 不要忘记给它锁上，目的是不要再让它继续写了

- 锁表的目的是不让表继续写，因为一会需要做从机器配置，需要进行一个同步，让两台机器同步，保证两台机器的数据一致，同步才不会出错

```

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

mysql>

```

- 做一个show master status；

```

mysql> show master status;

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

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

| aminglinux1.000001 | 10755 | | | |

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

1 row in set (0.00 sec)

mysql>

mysql> quit

Bye

[root@aming-01 mysql]#

```

- 需要记住binlog的filename，记住file 的位置，一会儿要用到它

- 一会儿要去从机器上同步这些库 (高亮的为库)

```

[root@aming-01 mysql]# ls

aming aminglinux1.index ib_logfile1 mysql2 zrlog

aming-01.err auto.cnf localhost.err performance_schema

aming-01.pid ibdata1 localhost.localdomain.err test

aminglinux1.000001 ib_logfile0 mysql www.qq123.com.err

[root@aming-01 mysql]#

[root@aming-01 mysql]# ls /tmp/zrlog.sql

/tmp/zrlog.sql

[root@aming-01 mysql]#

```

- 把这下面的库都做一个备份

```

[root@aming-01 mysql]# mysqldump -uroot -paminglinux mysql2 > /tmp/my2.sql

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

[root@aming-01 mysql]# ls /tmp/*sql

/tmp/blog,sql /tmp/blog.sql /tmp/my2.sql /tmp/zrlog.sql

[root@aming-01 mysql]#

```

# 17.4 配置从

- 安装mysql，已经做了

- 查看my.cnf，配置server-id=132，要求和主不一样

- 修改完配置文件后，启动或者重启mysqld服务

- 把主上aming库同步到从上

- 可以先创建aming库，然后把主上的/tmp/mysql.sql拷贝到从上，然后导入aming库

- mysql -uroot

- stop slave；

- change master to master_host='', master_user='repl', master_password='', master_log_file='', master_log_pos=xx,

- start slave;

- 还要到主上执行 unlock tables

- 进入/etc/my.cnf

```

[root@aming-02 ~]# vi /etc/my.cnf

[mysqld]

datadir=/data/mysql

socket=/tmp/mysql.sock

server-id=132

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Settings user and group are ignored when systemd is used.

# If you need to run mysqld under a different user or group,

# customize your systemd unit file for mariadb according to the

# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]

#log-error=/var/log/mariadb/mariadb.log

#pid-file=/var/run/mariadb/mariadb.pid

# include all files from the config directory

#!includedir /etc/my.cnf.d

:wq

```

- 重启服务

```

[root@aming-02 ~]# vi /etc/my.cnf

[root@aming-02 ~]# /etc/init.d/mysqld restart

Shutting down MySQL.. SUCCESS!

Starting MySQL.. SUCCESS!

[root@aming-02 ~]# ls /data/mysql

aming-02.err aminglinux1.000001 auto.cnf ib_logfile0 localhost.localdomain.err mysql test

aming-02.pid aminglinux1.index ibdata1 ib_logfile1 localhost.localdomain.pid performance_schema

```

- 保证和主从机器上的库数据一致

把主机器上备份的数据，拷贝到从机器上，然后做一个数据恢复

```

[root@aming-02 ~]# scp 192.168.202.131:/tmp/*.sql /tmp/

root@192.168.202.131's password:

blog.sql 100% 787 0.8KB/s 00:00

my2.sql 100% 642KB 641.9KB/s 00:00

zrlog.sql 100% 10KB 9.8KB/s 00:00

[root@aming-02 ~]#

[root@aming-02 ~]# mysql -uroot

-bash: mysql: 未找到命令

```

- 未找到命令，是因为没有把mysql命令加入到$PATH环境变量里面

- 为了方便使用mysql服务，将mysql目录、mysqldump加入到环境变量里,

```

[root@aming-02 ~]# alias 'mysql=/usr/local/mysql/bin/mysql'

[root@aming-02 ~]# alias 'mysqldump=/usr/local/mysql/bin/mysqldump'

[root@aming-02 ~]#

```

- 进入mysql

```

[root@aming-02 ~]# mysql -uroot

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.36 MySQL Community Server (GPL)

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>

```

- 创建4个库

```

mysql> create database aming;

Query OK, 1 row affected (0.00 sec)

mysql> create database zrlog;

Query OK, 1 row affected (0.00 sec)

mysql> create database blog;

Query OK, 1 row affected (0.00 sec)

mysql> create database mysql2;

Query OK, 1 row affected (0.00 sec)

mysql>

mysql> quit

Bye

[root@aming-02 ~]#

```

- 做一个恢复

```

[root@aming-02 ~]# mysql -uroot zrlog

[root@aming-02 ~]# mysql -uroot aming

[root@aming-02 ~]# mysql -uroot mysql2

[root@aming-02 ~]# mysql -uroot blog

[root@aming-02 ~]#

```

- 对比下俩边的文件

```

从上面

[root@aming-02 ~]# ls /data/mysql

aming aminglinux1.000001 blog ib_logfile1 mysql test

aming-02.err aminglinux1.index ibdata1 localhost.localdomain.err mysql2 zrlog

aming-02.pid auto.cnf ib_logfile0 localhost.localdomain.pid performance_schema

[root@aming-02 ~]#

主上面

[root@aming-01 mysql]# ls

aming aminglinux1.index ib_logfile0 mysql www.qq123.com.err

aming-01.err auto.cnf ib_logfile1 mysql2 zrlog

aming-01.pid blog localhost.err performance_schema

aminglinux1.000001 ibdata1 localhost.localdomain.err test

[root@aming-01 mysql]#

```

- 下面来实现主从

- 先进入mysql

```

[root@aming-02 ~]# mysql -uroot

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.6.36 MySQL Community Server (GPL)

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.

```

- stop slave;

```

mysql> stop slave;

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

mysql>

```

mysql> change master to master_host='192.168.202.131', master_user='repl', master_password='aminglinux111', master_log_file='aminglinux1.000001',master_log_pos=10755;

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

mysql>

mysql> start slave;

Query OK, 0 rows affected (0.02 sec)

mysql>

```

- 这个时候怎么判定主从有没有设置成功呢

```

mysql> start slave;

Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.202.131

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: aminglinux1.000001

Read_Master_Log_Pos: 10849

Relay_Log_File: aming-02-relay-bin.000002

Relay_Log_Pos: 285

Relay_Master_Log_File: aminglinux1.000001

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: 1007

Last_Error: Error 'Can't create database 'blog'; database exists' on query. Default database: 'blog'. Query: 'create database blog'

Skip_Counter: 0

Exec_Master_Log_Pos: 10755

Relay_Log_Space: 555

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: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 1007

Last_SQL_Error: Error 'Can't create database 'blog'; database exists' on query. Default database: 'blog'. Query: 'create database blog'

Replicate_Ignore_Server_Ids:

Master_Server_Id: 131

Master_UUID: 69e8b25a-b0dd-11e7-997f-000c292e28f2

Master_Info_File: /data/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State:

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp: 171107 23:15:40

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

mysql>

```

- Slave_IO_Running: Yes 这个很重要，其中一个是NO就表示主从已经端口

Slave_SQL_Running: No 这个很重要，其中一个是NO就表示主从已经端口

- 这里Slave_SQL_Running: No，出错了，

- 解决办法

```

mysql> stop slave;

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

mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

Query OK, 0 rows affected (0.00 sec)

mysql> start slave ;

Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.202.131

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: aminglinux1.000002

Read_Master_Log_Pos: 120

Relay_Log_File: aming-02-relay-bin.000007

Relay_Log_Pos: 285

Relay_Master_Log_File: aminglinux1.000002

Slave_IO_Running: Yes

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: 120

Relay_Log_Space: 626

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: 131

Master_UUID: 69e8b25a-b0dd-11e7-997f-000c292e28f2

Master_Info_File: /data/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

mysql>

```

- 恢复主机器上的表的写操作

unlock tables

```

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

mysql>

```

- 到此，主从就算搭建完了

# 17.5 测试主从同步

- 几个配置参数

- 主服务器上

- binlog-do-db= //仅同步指定的库(多个库，可以用“ , ”逗号分隔)

- binlog-ignore-db= //忽略指定库

- 从服务器上

- replicate_do_db= //仅同步指定的库

- replicate_ignore_db= //忽略指定库

- replicate_do_table= //仅同步指定的库建议用下面两条配置，请无视这条配置

- replicate_ignore_table= //忽略指定库建议用下面两条配置，请无视这条配置

- replicate_wild_do_table= //如aming.%, 支持通配符% 指定同步靠谱的匹配同步表常用

- replicate_wild_ignore_table= //如aming.%, 支持通配符% 指定同步靠谱的匹配忽略表常用

- 测试

主上进入mysql

切换到aming库

```

mysql> use aming;

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>

```

- 查看表

```

mysql> show tables;

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

| Tables_in_aming |

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

| comment |

| link |

| log |

| lognav |

| plugin |

| tag |

| type |

| user |

| website |

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

9 rows in set (0.00 sec)

mysql> select count(*) user;

+------+

| user |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

mysql>

```

- 再切换到从服务器上

```

mysql> use aming;

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> select count(*) user;

+------+

| user |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

mysql>

```

- 也是一样的，数据是一致的

- 现在要把tag表做一个删除操作

```

mysql> truncate table tag;

Query OK, 0 rows affected (0.03 sec)

mysql> select count(*) tag;

+-----+

| tag |

+-----+

| 1 |

+-----+

1 row in set (0.00 sec)

mysql>

mysql> select * from tag;

Empty set (0.00 sec)

mysql>

```

- 再去从上看下

```

mysql> select count(*) tag;

+-----+

| tag |

+-----+

| 1 |

+-----+

1 row in set (0.00 sec)

mysql> select * from tag;

Empty set (0.00 sec)

mysql>

```

- 下面做一个操作，把表直接给删掉

```

mysql> show tables;

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

| Tables_in_aming |

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

| comment |

| link |

| log |

| lognav |

| plugin |

| tag |

| type |

| user |

| website |

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

9 rows in set (0.00 sec)

mysql> drop table tag;

Query OK, 0 rows affected (0.03 sec)

mysql>

```

- 再去从上看

```

mysql> select * from tag;

ERROR 1146 (42S02): Table 'aming.tag' doesn't exist

mysql>

```

- 这就是主从同步

- 再来做个删除aming库

- 先去主上

```

mysql> drop database aming;

Query OK, 8 rows affected (0.15 sec)

mysql>

```

- 再去从上看，没有aming库了

```

mysql> show databases;

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

| Database |

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

| information_schema |

| blog |

| mysql |

| mysql2 |

| performance_schema |

| test |

| zrlog |

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

7 rows in set (0.00 sec)

mysql>

```

- [x] 因为意外在从机器上做了意外的删除，新增，更改操作，就可以可能导致“Slave_SQL_Running: NO”

- 如果出现了“Slave_SQL_Running: NO ” 需要去主上面，重新做一个“change master”的操作比对主机器上的“master_log_pos=10755”，就可以恢复了

- 操作前需要先sotp slave

- 然后再

- change master to master_host=’192.168.133.131′, master_user=’repl’, master_password=’aminglinux’,master_log_file=’Master.000001′, master_log_pos=10911;