8.31 17.1-17.5

最新推荐文章于 2021-07-31 23:54:07 发布

weixin_34050427

最新推荐文章于 2021-07-31 23:54:07 发布

阅读量68

点赞数

原文链接：http://blog.51cto.com/12216458/2169264

版权

17.1 MySQL主从介绍

MySQL主从即Replication、AB复制

MySQL主从是指两台机器的数据同步，A机器上写数据，B机器也会跟着写数据，实现两台机器数据实时同步

MySQL主从是基于binlog的：

binlog是一个文件，在文件中记录了一些日志

该文件为一个二进制文件，无法使用cat等命令查看

必须要在主从的主上开启binlog才能进行主从

主从过程步骤：

1 主将更改操作（比如对某库某表的update或insert等操作）记录到binlog中

2 从将主的binlog事件（sql语句）同步到从机器上，并记录在relaylog（从的一个日志文件，同样记录sql语句）中

3 从将relaylog中的sql语句按顺序执行

在主和从机器上共有三个线程：

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

从上有两个线程：

其中I/O线程用于同步主的binlog并生成relaylog

另一个sql线程用于把relaylog中的sql语句执行一遍

主从使用场景：

数据备份：从机器实时备份主的数据，一旦主机器故障，从机器可以马上上线继续提供服务

减轻主的压力：客户端读操作时，服务器可以将部分请求发给从，减轻主的压力，但向服务器写数据只能写在主上，否则会造成主从数据不一致

17.2 准备工作

确保主从机器均正确安装了mysql并可以正常运行

17.3 配置主

主的相关操作

修改主的配置文件my.cnf

[root@hyc-01-01 ~]# vim /etc/my.cnf

[mysqld]

federated

datadir=/data/mysql

socket=/tmp/mysql.sock

server-id=129 指定server-id，server-id无特殊要求

log_bin=hyc1 打开binlog，指定binlog的前缀

# 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

…

重启mysql

[root@hyc-01-01 ~]# service mysqld restart

Shutting down MySQL.. SUCCESS!

Starting MySQL.. SUCCESS!

查看/data/mysql

[root@hyc-01-01 ~]# ls -l /data/mysql

总用量 110676

-rw-rw---- 1 mysql mysql 56 8月 11 22:11 auto.cnf

-rw-rw---- 1 mysql mysql 55782 9月 2 20:07 hyc-01-01.err

-rw-rw---- 1 mysql mysql 5 9月 2 20:07 hyc-01-01.pid

-rw-rw---- 1 mysql mysql 120 9月 2 20:07 hyc1.000001

二进制binlog文件，可能生成多个

-rw-rw---- 1 mysql mysql 14 9月 2 20:07 hyc1.index 索引文件

-rw-rw---- 1 mysql mysql 12582912 9月 2 20:08 ibdata1

-rw-rw---- 1 mysql mysql 50331648 9月 2 20:08 ib_logfile0

-rw-rw---- 1 mysql mysql 50331648 8月 11 21:52 ib_logfile1

drwx------ 2 mysql mysql 4096 8月 11 21:52 mysql

drwx------ 2 mysql mysql 4096 8月 25 23:01 mysql2

drwx------ 2 mysql mysql 4096 8月 11 21:52 performance_schema

drwx------ 2 mysql mysql 20 8月 25 21:34 test1

drwx------ 2 mysql mysql 324 9月 1 19:40 zrlog

所有以hyc1为前缀的文件对实现主从非常重要，没有这些文件主从无法完成

准备测试环境

备份数据库测试时使用

[root@hyc-01-01 ~]# mysqldump -uroot -p123456 zrlog > zrlog.sql

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

[root@hyc-01-01 ~]# du -sh zrlog.sql

12K zrlog.sql

创建新的库

[root@hyc-01-01 ~]# mysql -uroot -p123456 -e "create database hyc"

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

将zrlog的备份恢复到库hyc

[root@hyc-01-01 ~]# mysql -uroot -p123456 hyc < zrlog.sql

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

执行完上述操作后再执行ls -l /data/mysql，会发现文件hyc1.000001变大，因为该文件需要完整记录数据库创建过程（包括创建的库、表及表中的数据）

用户可以通过binlog文件恢复表中的数据

创建用户用于主从同步

mysql> grant replication slave on *.* to 'repl'@'192.168.31.128' identified by 'hyc940421';

进入mysql数据库

创建用户repl用于主从复制时从登录到主服务器时使用

创建用户repl指定从192.168.31.128登录，登录密码为hyc940421，授予该用户对所有库的所有表的replication slave权限

Query OK, 0 rows affected (0.00 sec)

锁表（拒绝再向表写数据）

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

锁表的目的是为了中止写操作，因为从要同步备份的数据，使两者保持一致，这样才能实现同步

mysql> show master status；

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

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

| hyc1.000001 | 12077 | | | |

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

1 row in set (0.00 sec)

这里需要记住binlog文件的filename和position

使用mysqldump将库hyc、mysql2、test1、zrlog备份到tmp目录

[root@hyc-01-01 tmp]# mysqldump -uroot -p123456 mysql2 > /tmp/my2.sql

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

[root@hyc-01-01 tmp]# mysqldump -uroot -p123456 test1 > /tmp/test1.sql

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

[root@hyc-01-01 tmp]# mysqldump -uroot -p123456 hyc > /tmp/hyc.sql

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

[root@hyc-01-01 tmp]# mysqldump -uroot -p123456 zrlog > /tmp/zrlog.sql

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

17.4 配置从

编辑mysql配置文件

[root@hyc-01 usr]# vim /etc/my.cnf

…

# *** upgrade to a newer version of MySQL.

[mysqld]

datadir=/data/mysql

socket=/tmp/mysql.sock

server-id=128

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

…

server-id不能和主一致

从不需要配置binlog

重启mysql

将主服务器上的数据库备份文件拷贝到从上

[root@hyc-01 usr]# scp 192.168.31.129:/tmp/*.sql /tmp/

root@192.168.31.129's password:

hyc.sql 100% 11KB 3.1MB/s 00:00

my2.sql 100% 646KB 39.8MB/s 00:00

test1.sql 100% 1260 547.7KB/s 00:00

zrlog.sql

创建对应的几个数据库

[root@hyc-01 ~]# mysql -uroot

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

Your MySQL connection id is 3

Server version: 5.6.39 MySQL Community Server (GPL)

…

mysql> create database hyc;

Query OK, 1 row affected (0.00 sec)

mysql> create database mysql2;

Query OK, 1 row affected (0.00 sec)

mysql> create database test1;

Query OK, 1 row affected (0.00 sec)

mysql> create database zrlog;

Query OK, 1 row affected (0.00 sec)

将对应数据恢复到对应的库

[root@hyc-01 tmp]# mysql -uroot hyc < /tmp/hyc.sql

[root@hyc-01 tmp]# mysql -uroot mysql2 < /tmp/my2.sql

[root@hyc-01 tmp]# mysql -uroot test1 < /tmp/test1.sql

[root@hyc-01 tmp]# mysql -uroot zrlog < /tmp/zrlog.sql

这里需要保证恢复后两边库的数据一致

登录从的mysql执行操作：

mysql> stop slave;

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

mysql> change master to master_host='192.168.31.129', master_user='repl', master_password='hyc940421', master_log_file='hyc1.000001', master_log_pos=12077;

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

master_host='192.168.31.129' 主的地址

master_user='repl' 登录主使用的用户

master_password='hyc940421' 用户的密码

master_log_file='hyc1.000001' 以下两个信息可以在主上使用show master status查看

master_log_pos=12077

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.31.129

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: hyc1.000001 binlog文件

Read_Master_Log_Pos: 12077

Relay_Log_File: hyc-01-relay-bin.000002 relaylog文件（中继日志）

Relay_Log_Pos: 278

Relay_Master_Log_File: hyc1.000001

Slave_IO_Running: Yes 此处两个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: 12077

Relay_Log_Space: 452

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

…

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 129

Master_UUID: 7efbd772-9d70-11e8-a029-000c294d9d95

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

…

1 row in set (0.00 sec)

ERROR:

No query specified

在主服务器上解除锁表

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

总结从上的操作：

修改mysql配置文件添加server-id

将主的数据同步到从，保证主从数据一致，此时最好将主锁表，拒绝所有写操作

在从的mysql下：

Stop slave

change master to master_host='192.168.31.129', master_user='repl', master_password='hyc940421', master_log_file='hyc1.000001', master_log_pos=12077;

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

Start slave

在主的mysql下：

Unlock tables

17.5 测试主从同步

相关配置参数：

主服务器

binlog-do-db= 设置成仅同步指定的库，指定多个库时可用，分割

binlog-ignore-db= 忽略某些库，同步其余的库

从服务器

replicate_do_db= 设置仅同步指定的库，指定多个库时用，分割

replicate_ignore_db= 忽略某些库，同步其余的库

replicate_do_table= 同步指定的表

replicate_ignore_table= 忽略某些表

假如用户忽略了mysql库，那么use mysql后的update、insert等操作将被忽略不再被记录到relaylog中，此时假如有一个联合查询用到了语句select zrlog.*，那么该select语句也将被忽略，这会导致同步的数据不完整

replicate_wild_do_table=

replicate_wild_ignore_table=

以上两个参数可以在=后面写hyc.%表示hyc库所有的表，第一个表示所有表都同步，第二个表示所有表都忽略

由于以上两个参数支持库.表（hyc.*）,一般可以尽量避免使用replicate_do_table和replicate_ignore_table

测试主从

在主上：

mysql> use hyc;

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_hyc |

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

| comment |

| link |

| log |

| lognav |

| plugin |

| tag |

| type |

| user |

| website |

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

9 rows in set (0.00 sec)

mysql> select count(*) tag;

+-----+

| tag |

+-----+

| 1 |

+-----+

1 row in set (0.00 sec)

mysql> truncate table tag;

Query OK, 0 rows affected (0.04 sec)

mysql> select count(*) tag;

+-----+

| tag |

+-----+

| 1 |

+-----+

1 row in set (0.00 sec)

mysql> drop table tag; 在主上直接删掉hyc.tag表

Query OK, 0 rows affected (0.03 sec)

在从上：

mysql> select count(*) from hyc.tag;

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

| count(*) |

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

| 1 | 与主上数据一致，均为一行

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

1 row in set (0.01 sec)

主上执行truncate后

mysql> select count(*) from hyc.tag;

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

| count(*) |

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

| 0 | 此时从上的数据变为0行

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

1 row in set (0.00 sec)

mysql> select * from hyc.tag;

Empty set (0.00 sec)

主上执行drop table后

mysql> select * from hyc.tag; 在主上该表被删除，在从上该表也消失

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

问题：

在从上

mysql> drop database hyc;

Query OK, 8 rows affected (0.10 sec)

mysql>show slave status\G

…

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

Last_Error: Error 'Can't drop database 'hyc'; database doesn't exist' on query. Default database: 'hyc'. Query: 'drop database hyc'

…

如果用户在从上删了某个库，此时用户再到主上删除相同的库就会出现报错；因为用户在主上删除库时，从上会将该删除库的操作再在从上执行一遍，但此时从上已经没有对应的库了，此时遇到问题，主从断开

解决：

在从上

mysql> stop slave;

Query OK, 0 rows affected (0.02 sec)

mysql> start slave;

Query OK, 0 rows affected (0.03 sec)

mysql> show slave status\G

…

Slave_IO_Running: Yes

Slave_SQL_Running: No

…

若以上操作未解决则需要重做主从：

在主上

mysql> show master status;

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

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

| hyc1.000001 | 12370 | | | |

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

1 row in set (0.00 sec)

再从上

mysql> stop slave;

Query OK, 0 rows affected (0.01 sec)

mysql> change master to master_host='192.168.31.129', master_user='repl', master_password='hyc940421', master_log_file='hyc1.000001', master_log_pos=12370;

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

此时由于主和从的数据一致，所以只要将变化的position改成一样的，再重新start slave即可

mysql> show slave status\G

…

Read_Master_Log_Pos: 12370

Relay_Log_File: hyc-01-relay-bin.000002

Relay_Log_Pos: 278

Relay_Master_Log_File: hyc1.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

…

转载于:https://blog.51cto.com/12216458/2169264

weixin_34050427

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
8.31 17.1-17.5

17.1 MySQL主从介绍MySQL主从即Replication、AB复制MySQL主从是指两台机器的数据同步，A机器上写数据，B机器也会跟着写数据，实现两台机器数据实时同步MySQL主从是基于binlog的：binlog是一个文件，在文件中记录了一些日志该文件为一个二进制文件，无法使用cat等命令查看必须要在主从的主上开启binlog才能进行主从主从过程步骤：1 主...
复制链接

扫一扫