版权声明:本文为耕耘实录原创文章,各大自媒体平台同步更新。欢迎转载,转载请注明出处,谢谢
1.两台mysql都可读写,互为主备。本文的实验中:默认只使用一台(DCGH-DB1)负责数据的写入,另一台(DCGH-DB2)备用。
2.DCGH-DB1是DCGH-DB2的主库,DCGH-DB2又是DCGH-DB1的主库,它们互为主从。
3.不足之处:DCGH-DB2可能会一直处于空闲状态(后期经过改进后,可以用它当从库,负责部分查询)。
操作系统:CentOS Linux release 7.4.1708 (Core) 64Bit MySQL版本:MySQL Community Server version: 5.7.21 64Bit 主机名及IP:DCGH-DB1(10.1.1.31),DCGH-DB2(10.1.1.32) 两台主机的操作系统,数据库版本完全一致(克隆的)。实际环境中,需要确保操作系统版本完全一致、MySQL版本完全一致、数据完全一致,可能会涉及到数据库的备份与还原。
1.在DCGH-DB2上安装MySQL服务器,进行初始化。
[root@DCGH-DB2 ~]# curl -C - -O https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm [root@DCGH-DB2 ~]# rpm -ivh mysql57-community-release-el7-11.noarch.rpm [root@DCGH-DB2 ~]# yum -y install mysql-community-server [root@DCGH-DB2 ~]# systemctl start mysqld [root@DCGH-DB2 ~]# systemctl enable mysqld [root@DCGH-DB2 ~]# firewall-cmd --permanent --add-port=3306/tcp [root@DCGH-DB2 ~]# firewall-cmd --reload [root@DCGH-DB2 ~]# grep -i password /var/log/mysqld.log 2018-04-11T10:11:40.732848Z 1 [Note] A temporary password is generated for root@localhost: p>-/fQDca3ag [root@DCGH-DB2 ~]# mysql -u root -p Enter password: mysql> set password=password("DCGH-test-db2"); mysql> exit
如果遭遇如下错误:ERROR 1819 (HY000): Your password does not satisfy the current policy requirements,密码不满足当前策略要求,那么请执行以下命令:
set global validate_password_policy=0; set global validate_password_mixed_case_count=0; set global validate_password_number_count=3; set global validate_password_special_char_count=0; set global validate_password_length=3;
2.关机克隆DCGH-DB2,修改配置,使之满足DCGH-DB1要求,只需修改主机名及IP即可,登录数据库,修改密码。
[root@DCGH-DB2 ~]# hostnamectl set-hostname DCGH-DB1 --static [root@DCGH-DB2 ~]# nmtui [root@DCGH-DB2 ~]# systemctl restart network [root@DCGH-DB1 ~]# mysql -u root -p Enter password: mysql> set password=password("DCGH-test-db1"); mysql> exit
至此,实验环境准备完毕。
1.修改/etc/my.cnf,在[mysqld]下加入如下内容:
server-id=31 auto-increment-increment=2 auto-increment-offset=1 log-bin=mysql-bin binlog_format=mixed relay-log=relay-bin log-slave-updates
2.重启服务,创建同步用户copy,并授予相应权限,锁表,查出Position备用。
[root@DCGH-DB1 ~]# mysql -u root -A -p Enter password: mysql> grant replication slave,replication client on *.* to 'copy'@10.1.1.32 identified by 'DCGH-test-db2'; mysql> flush privileges; mysql> flush tables with read lock; mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 627 | | | | +------------------+----------+--------------+------------------+-------------------+
1.修改/etc/my.cnf,在[mysqld]下加入如下内容:
server-id=32 auto-increment-increment=2 auto-increment-offset=1 log-bin=mysql-bin binlog_format=mixed relay-log=relay-bin log-slave-updates
2.重启服务,创建同步用户copy,并授予相应权限并在DCGH-DB2上去同步DCGH-DB1上的数据。
[root@DCGH-DB2 ~]# mysql -u root -A -p Enter password: mysql> grant replication slave,replication client on *.* to 'copy'@10.1.1.31 identified by 'DCGH-test-db1'; mysql> CHANGE MASTER TO MASTER_HOST='10.1.1.31',MASTER_USER='copy',MASTER_PASSWORD='DCGH-test-db2',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=627; mysql> start slave; mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Master_Host: 10.1.1.31 Master_User: copy Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 627 Relay_Log_File: relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: No 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: 627 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: NULL 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 UUIDs; these UUIDs must be different for replication to work. Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 31 Master_UUID: Master_Info_File: /var/lib/mysql/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: 180411 19:27:07 Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:
致命报错来袭:Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different。原来是UUID重复了,这是因为咱们的MySQL服务器是克隆出来的,好办,改server-uuid(随便改个数字或字母跟之前uuid不一致即可)!
[root@DCGH-DB2 ~]# vi /var/lib/mysql/auto.cnf server-uuid=ba5f1c18-3d70-11e8-891f-000c2986a1f0 [root@DCGH-DB2 ~]# systemctl restart mysqld
重复以上步骤:
mysql> CHANGE MASTER TO MASTER_HOST='10.1.1.31',MASTER_USER='copy',MASTER_PASSWORD='DCGH-test-db2',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=627; mysql> start slave; mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.1.1.31 Master_User: copy Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 627 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 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: 627 Relay_Log_Space: 521 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: 31 Master_UUID: ba5f1c18-3d70-11e8-891f-000c2986a1f9 Master_Info_File: /var/lib/mysql/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: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:
Slave_IO_Running: Yes,Slave_SQL_Running: Yes,Seconds_Behind_Master: 0,表明目前已经同步了。
1.回到DCGH-DB1,解锁表创建数据库DCGHDB。
mysql> unlock tables; mysql> create database DCGHDB; mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | DCGHDB | | mysql | | performance_schema | | sys | +--------------------+
我在DCGHDB2使用show databases命令,结果一致。 2.在DCGH-DB1创建表并插入数据,看是否同步。
mysql> use DCGHDB; Database changed mysql> create table test(username varchar(30),password varchar(30)); mysql> insert into test(username,password) values('IVAN DU','DCGH-test'); mysql> select * from test; +----------+-----------+ | username | password | +----------+-----------+ | IVAN DU | DCGH-test | +----------+-----------+
在DCGH-DB2查表test的数据是否一致。
mysql> use DCGHDB; Database changed mysql> select * from test; +----------+-----------+ | username | password | +----------+-----------+ | IVAN DU | DCGH-test | +----------+-----------+
3.在DCGH-DB1更新表test。
mysql> update test set username='DCGH' where username='IVAN DU'; mysql> select * from test; +----------+-----------+ | username | password | +----------+-----------+ | DCGH | DCGH-test | +----------+-----------+
在DCGH-DB2进行查看,结果一致。
mysql> select * from test; +----------+-----------+ | username | password | +----------+-----------+ | DCGH | DCGH-test | +----------+-----------+
4.经验证,删除表数据、表结构、库都能够顺利同步。不展示,相关验证命令如下:
mysql> delete from test; mysql> drop table test; mysql> drop database DCGHDB; mysql> show tables;
配置文件部分选项解读及拓展:
# 为每个 session 分配的内存,在事务过程中用来存储二进制日志的缓存 binlog_cache_size=1M # 主从复制的格式(mixed,statement,row,默认格式是 statement) binlog_format=mixed ## 跳过主从复制中遇到的所有错误或指定类型的错误,避免 slave 端复制中断。 ## 如:1062 错误是指一些主键重复,1032 错误是因为主从数据库数据不一致 slave_skip_errors=1062 # 作为从服务器时的中继日志 relay_log=edu-mysql-relay-bin # log_slave_updates 表示 slave 将复制事件写进自己的二进制日志 log_slave_updates=1 # 主键自增规则,避免主从同步ID重复的问题 auto_increment_increment=2 # 自增因子(每次加2) auto_increment_offset=1 # 自增偏移(从1开始),单数
MySQL数据库配置文件的注意点:
[mysqld] server-id = 1 #[必须]服务器唯一ID,每台服务器需不同 log-bin = /home/mysql/mysql-bin #[必须]启用二进制文件 binlog_format = mixed #[可选]二进制文件启用混合模式 expire-logs-days = 14 #[可选]二进制日志文件过期时间,单位是天 sync-binlog = 1 #[可选]当每进行1次事务提交之后,MySQL将进行一次磁盘同步指令来将binlog_cache中的数据强制写入磁盘 # MASTER DB # binlog-do-db = test,androidpnserver #[可选]只将对应的数据库变动写入二进制文件。如果有多个数据库可用逗号分隔,或者使用多个binlog-do-db选项 binlog-ignore-db = mysql,information_schema,performance_schema #[必须]不需要记录二进制日志的数据库。如果有多个数据库可用逗号分隔,或者使用多个binlog-do-db选项。一般为了保证主主同步不冲突,会忽略mysql数据库。 auto-increment-increment = 10 #[必须] auto-increment-offset = 1 #[必须] #做主主备份的时候,因为每台数据库服务器都可能在同一个表中插入数据,如果表有一个自动增长的主键,那么就会在多服务器上出现主键冲突。 #解决这个问题的办法就是让每个数据库的自增主键不连续。上面两项说的是,假设需要将来可能需要10台服务器做备份,将auto-increment-increment设为10。而auto-increment-offset=1表示这台服务器的序号。从1开始,不超过auto-increment-increment。 # SLAVE DB # replicate-do-db = test,androidpnserver #[可选]只同步对应的数据库。如果有多个数据库可用逗号分隔,或者使用多个replicate-do-db选项 replicate-ignore-db = mysql,information_schema,performance_schema #[必须]不需要同步的数据库。如果有多个数据库可用逗号分隔,或者使用多个replicate-ignore-db选项。一般为了保证主主同步不冲突,会不同步mysql数据库。 relay_log = /home/mysql/relay-bin #[可选]开启中继日志,复制线程先把远程的变化复制到中继日志中,再执行。 log-slave-updates = ON #[必须]中继日志执行之后将变化写入自己的二进制文件 slave-skip-errors = all #[可选]跳过所有的sql
https://dev.mysql.com/doc/refman/5.6/en/replication-howto-newservers.html https://dev.mysql.com/doc/refman/5.7/en/stored-programs-logging.html https://dev.mysql.com/doc/refman/5.7/en/replication.html
本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。
我来说两句