mysql之主从同步_mysql之主从同步

最新推荐文章于 2021-02-17 13:26:15 发布

张匡龙

最新推荐文章于 2021-02-17 13:26:15 发布

阅读量84

点赞数

文章标签： mysql之主从同步

本文链接：https://blog.csdn.net/weixin_35733495/article/details/113650332

版权

主服务器配置文件:[root@martin data]# vim /etc/my.cnf

[mysqld]

basedir = /usr/local/mysql

datadir = /mydata/mysql/data

port = 3306

server_id = 1 ＃主id

socket = /tmp/mysql.sock

log-bin=/mydata/mysql/log-bin/master-bin ＃二进制日记

binlog_format=row ＃能够精确完成有着触发器、存储过程等代码场景中的复制；能完成几乎所有的复制功能；较少的CPU占用率；

无法判断执行了什么样的SQL语句；数据量可能略大；

innodb_file_per_table=ON ＃innodb数据与索引分离存储

#if slave exist

sync_binlog=1 ＃立即同步提交的事务

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES#添加数据同步账号

mysql> grant replication slave,replication client on *.* to ‘master‘@‘192.168.%.%‘ identified by ‘222222‘;

mysql> flush privileges;

从服务器配置文件：[root@lucia mysql]# vim /etc/my.cnf

[mysqld]

basedir = /usr/local/mysql

datadir = /mydata/mysql/data

port = 3306

socket = /tmp/mysql.sock

#log-bin=/mydata/mysql/log-bin/master-bin

binlog_format=row

innodb_file_per_table=ON

server_id = 2 ＃从id 区别于主

relay-log=/mydata/mysql/relay-bin/relay-bin

read_only=ON ＃非管理员用户只能读

#safe but slow

sync_master_info = 1

sync_relay_log = 1

sync_relay_log_info = 1

slave-parallel-workers=2 ＃开启多进程复制

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

配置查看：[root@lucia mysql]# mkdir relay-bin

[root@lucia mysql]# chown -R mysql.mysql relay-bin/

mysql> show global variables like ‘%relay%‘;

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

| Variable_name | Value |

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

| max_relay_log_size | 0 |

| relay_log | /mydata/mysql/relay-bin |

| relay_log_basename | /mydata/mysql/relay-bin |

| relay_log_index | /mydata/mysql/relay-bin.index |

| relay_log_info_file | relay-log.info |

| relay_log_info_repository | FILE |

| relay_log_purge | ON |

| relay_log_recovery | OFF |

| relay_log_space_limit | 0 |

| sync_relay_log | 10000 |

| sync_relay_log_info | 10000 |

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

从服务器连接主服务器：mysql> change master to master_host=‘192.168.1.222‘,master_user=‘master‘,master_password=‘222222‘;mysql> show slave status\G

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

Slave_IO_State:

Master_Host: 192.168.1.222

Master_User: master

Master_Port: 3306

Connect_Retry: 60

Master_Log_File:

Read_Master_Log_Pos: 4

Relay_Log_File: relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File:

Slave_IO_Running: No

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

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

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 0

Master_UUID:

Master_Info_File: /mydata/mysql/data/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:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0mysql> start slave;

模拟两台数据不一致的主机进行同步：＃主服务器

[root@martin ~]# mysqldump -A --flush-logs --master-data=2 -x -uroot -hlocalhost -p>all.sql

[root@martin ~]# scp -P6789 all.sql root@lucia:~/

＃从服务器从all.sql中查询日志信息

[root@lucia relay-bin]# mysql

mysql> change master to master_host=‘192.168.1.222‘,master_user=‘master‘,master_password=‘222222‘,master_log_file=‘master-bin.000012‘,master_log_pos=120;

mysql> start slave; #开启mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.222

Master_User: master

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000012

Read_Master_Log_Pos: 120

Relay_Log_File: relay-bin.000002

Relay_Log_Pos: 284

Relay_Master_Log_File: master-bin.000012

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

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: 6d2a2a10-2198-11e6-aded-000c294b9b35

Master_Info_File: /mydata/mysql/data/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

从服务器停止响应与重置：mysql> stop slave;

mysql> reset slave;

注意：服务器重启后，从服务器自动开启

优化：

半同步复制:(一旦某次等待超时，会自动降级为异步)

主服务器mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so‘;

mysql> SHOW GLOBAL VARIABLES LIKE ‘%semi%‘;

mysql> SET GLOBAL rpl_semi_sync_master_enabled=ON;

mysql> SET GLOBAL rpl_semi_sync_master_timeout=2000;

从服务器：mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so‘;

mysql> SET GLOBAL rpl_semi_sync_slave_enabled=ON;

mysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;

在主服务器验正半同步复制是否生效：mysql> SHOW GLOBAL STATUS LIKE ‘%semi%‘;

是否延迟：(允许波动)mysql> show slave status\G

.......

Seconds_Behind_Master: 0 #时常

卸载：mysql>unstall PLUGIN (rpl_semi_sync_slave | rpl_semi_sync_master)

实验结果：

主服务器：mysql> select * from t4;

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

| id | data | expire |

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

| 1 | 22 | 0 |

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

1 row in set (0.00 sec)

mysql> insert into t4(id,data)values(2,33);

Query OK, 1 row affected (0.00 sec)

mysql> select * from t4;

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

| id | data | expire |

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

| 1 | 22 | 0 |

| 2 | 33 | 0 |

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

从服务器：mysql> select * from t4;

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

| id | data | expire |

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

| 1 | 22 | 0 |

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

1 row in set (0.00 sec)

mysql> select * from t4;

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

| id | data | expire |

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

| 1 | 22 | 0 |

| 2 | 33 | 0 |

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

2 rows in set (0.00 sec)

over

mysql之主从同步

标签：mysql主从同步复制

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：http://9173436.blog.51cto.com/9163436/1783214

张匡龙

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql之主从同步_mysql之主从同步

主服务器配置文件:[root@martindata]#vim/etc/my.cnf[mysqld]basedir=/usr/local/mysqldatadir=/mydata/mysql/dataport=3306server_id=1＃主idsocket=/tmp/mysql.socklog-bin=/mydata/mysql/log-bin/master-bi...
复制链接

扫一扫