mysql主从搭建

背景

后台工程师兼职做系统运维工程师不容易，码字留念。入司前，研发团队后台开发一个都没有，入职时跟一群外包同事摸爬滚打3个月，算是工作交接，因为外包朋友算是拿多少钱干多少活的主，所以数据库这块一直是单点部署，有时候做梦都梦见主库挂掉啦，数据无法恢复，直接打包走人的场景，本着数据是一个公司的生命的认知力，决定把数据库这块做个实时备份的从库，然后就在网上各种搜爬，网上教程倒不少，但是跟着配了很多方案都没有配置成功,最后还是借助以前老哥 @威哥 的淫威之下才把任务完成。下面我们开始吧。

环境配置

|名称|版本|IP|备注|配置|

|cengtos|6.8|192.168.199.129|master|1核2g虚拟机|

|cengtos|6.8|192.168.199.131|slave|1核2g虚拟机|

|mysql|5.6.33|-|mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz|官网下载|

安装mysql

• 解压

tar -zxvf mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz

cp -r mysql-5.6.33-linux-glibc2.5-x86_64 /usr/local/mysql

• 添加用户组和用户

#添加用户组
groupadd mysql
#添加用户mysql 到用户组mysql
useradd -g mysql mysql

• 安装

#创建mysql数据存储目录

cd /usr/local/mysql/
mkdir ./data/mysql
#修改成mysql权限
chown -R mysql:mysql ./
#
./scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data/mysql
#把mysqld服务加到系统服务中
cp support-files/mysql.server /etc/init.d/mysqld
#修改可执行权限
chmod 755 /etc/init.d/mysqld
#mysql启动配置文件默认读取路径
cp support-files/my-default.cnf /etc/my.cnf

#修改启动脚本
vi /etc/init.d/mysqld
#修改项：
basedir=/usr/local/mysql/

datadir=/usr/local/mysql/data/mysql
#启动服务
service mysqld start
#测试连接
./mysql/bin/mysql -uroot
#加入环境变量，编辑 /etc/profile，这样可以在任何地方用mysql命令了
export PATH=$PATH:/usr/local/mysql//bin
source /etc/profile
#启动mysql
service mysqld start
#关闭mysql
service mysqld stop
#查看运行状态
service mysqld status

#授权

use mysql;
CREATE USER canal IDENTIFIED BY 'salve';
#只读权限从库用户权限
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'salve'@'%';
-- GRANT ALL PRIVILEGES ON *.* TO 'slave'@'%' ;
FLUSH PRIVILEGES;
或
GRANT ALL PRIVILEGES ON *.* TO `salve`@'%’ IDENTIFIED BY ‘youpassword’ WITH GRANT OPTION;
FLUSH PRIVILEGES;

两台服务器都执行以上安装操作

主从配置

• 方式:GTID方式

GTID
Global transaction identifiers
可以理解为一个事务对应一个唯一ID
一个GTID在一个服务器上只会执行一次
GTID是用来代替传统复制的方法
MySQl-5.6.2开始支持，5.6.10后完善了
GTID的组成
Server_uuid:sequence number

• 配置注意

修改主库192.168.199.129:/etc/my.cnf文件，需要加入以下参数 需要放在[mysqld]下面

gtid_mode=on

enforce-gtid-consistency=on

server-id = 813316

log-bin = /usr/local/mysql/data

binlog_format = row

log-slave-updates=1

binlog-gtid-simple-recovery=1

• 关闭/重启服务

[root@localhost ~]# service mysqld restart

Starting MySQL.... SUCCESS!

• 登录mysql，查询主库配置信息是否生效

[root@localhost etc]# mysql -uroot

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

Your MySQL connection id is 1

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



Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.



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> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| data.000004 | 191 | | | fd736651-d0a2-11e9-b357-000c293bc199:1-3 |

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

1 row in set (0.00 sec)

• 修改从库配置信息，192.168.199.131:/etc/my.cnf

gtid_mode=on

enforce-gtid-consistency=on

server-id = 813317

log-bin = /usr/local/mysql/data

binlog_format = row

log-slave-updates=1

binlog-gtid-simple-recovery=1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

• 启动后查看GTID是否生效

[root@zhanghp2 etc]# mysql -uroot

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

Your MySQL connection id is 4

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



Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.



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> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| data.000004 | 191 | | | fd736651-d0a2-11e9-b357-000c293bc199:1-3 |

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

1 row in set (0.00 sec)

以上配置结束，下面开始导出原有数据，然后倒入到从库中。

• 导出数据

#-A是导出全库， 可以把-A换成数据库的名字， 比如liuxn liuxn3316

/usr/local/mysql/bin/mysqldump -uroot -pchenw44 -S /tmp/mysql.sock --master-data=2 --single-transaction -A >/home/mysql/db.sql

• 导入数据

#注意如果是一个坏掉的库可能会报错是一个关于GTID的值应该为空的错误提示,需要在库上执行 mysql> reset master

#查询gtid是否开启成功

show global variables like "%gtid%";

#导入

mysql -S /tmp/mysql3317.sock </home/mysql/db.sql

#导入后由于mysql库也导入了，需要这样连接进入

mysql -uroot -S /tmp/mysql3307.sock

• 从库配置开始同步

#清除之前的从库配置信息

reset slave all

#master_auto_position可以自动开启查找日志功能， 再自己查找了

change master to master_host='10.10.1.81',MASTER_PORT=3316,master_user='repl',master_password='repl4slave',master_auto_position=1;

• 查看同步配置show slave statusG;

mysql> show slave status\G;

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

               Slave_IO_State: 

                  Master_Host: 192.168.199.129

                  Master_User: root

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: data.000002

          Read_Master_Log_Pos: 1913

               Relay_Log_File: zhanghp2-relay-bin.000003

                Relay_Log_Pos: 4

        Relay_Master_Log_File: data.000002

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

              Relay_Log_Space: 240

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

                Last_IO_Error: error connecting to master '[email protected]:3306' - retry-time: 60 retries: 44

               Last_SQL_Errno: 0

               Last_SQL_Error: 

  Replicate_Ignore_Server_Ids: 

             Master_Server_Id: 0

                  Master_UUID: fd736651-d0a2-11e9-b357-000c293bc199

             Master_Info_File: /usr/local/mysql/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: 190912 18:42:07

     Last_SQL_Error_Timestamp: 

               Master_SSL_Crl: 

           Master_SSL_Crlpath: 

           Retrieved_Gtid_Set: 

            Executed_Gtid_Set: fd736651-d0a2-11e9-b357-000c293bc199:1-3

                Auto_Position: 1

1 row in set (0.00 sec)



ERROR: 

No query specified

• 开始同步start slave

mysql> start slave;

Query OK, 0 rows affected (0.08 sec)

• 查看同步状态

mysql> show slave status\G;

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.199.96.147

                  Master_User: root

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: data.000001

          Read_Master_Log_Pos: 344899576

               Relay_Log_File: goodairnbapp04-relay-bin.000005

                Relay_Log_Pos: 330811707

        Relay_Master_Log_File: data.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: 344899576

              Relay_Log_Space: 330811889

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

                  Master_UUID: 641e091b-93fb-11e8-a27d-801844ee17a0

             Master_Info_File: /export/mysql/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: 641e091b-93fb-11e8-a27d-801844ee17a0:1842-304615

            Executed_Gtid_Set: 641e091b-93fb-11e8-a27d-801844ee17a0:1-304615



#  Retrieved_Gtid_Set: 641e091b-93fb-11e8-a27d-801844ee17a0:1842-304615

表示从第1842个开始接收现在是第304615个

# Executed_Gtid_Set: 641e091b-93fb-11e8-a27d-801844ee17a0:1-304615

表示执行了304615个事务

验证

• 在主库test库上建一个新表测试

CREATE TABLE `auth_client` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `code` varchar(255) DEFAULT NULL COMMENT '服务编码',

  `secret` varchar(255) DEFAULT NULL COMMENT '服务密钥',

  `name` varchar(255) DEFAULT NULL COMMENT '服务名',

  `locked` char(1) DEFAULT NULL COMMENT '是否锁定',

  `description` varchar(255) DEFAULT NULL COMMENT '描述',

  `crt_time` datetime DEFAULT NULL COMMENT '创建时间',

  `crt_user` varchar(255) DEFAULT NULL COMMENT '创建人',

  `crt_name` varchar(255) DEFAULT NULL COMMENT '创建人姓名',

  `crt_host` varchar(255) DEFAULT NULL COMMENT '创建主机',

  `upd_time` datetime DEFAULT NULL COMMENT '更新时间',

  `upd_user` varchar(255) DEFAULT NULL COMMENT '更新人',

  `upd_name` varchar(255) DEFAULT NULL COMMENT '更新姓名',

  `upd_host` varchar(255) DEFAULT NULL COMMENT '更新主机',

  `attr1` varchar(255) DEFAULT NULL,

  `attr2` varchar(255) DEFAULT NULL,

  `attr3` varchar(255) DEFAULT NULL,

  `attr4` varchar(255) DEFAULT NULL,

  `attr5` varchar(255) DEFAULT NULL,

  `attr6` varchar(255) DEFAULT NULL,

  `attr7` varchar(255) DEFAULT NULL,

  `attr8` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4

• 在从库查询是否同步

mysql> show tables;

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

| Tables_in_test |

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

| auth_client |

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

1 row in set (0.00 sec)

bingo生效啦！！！

A & Q

• mysql启动不成功，查看mysql日志

190910 16:55:17 mysqld_safe mysqld from pid file /export/mysql/data/mysql/goodairnbapp03.pid ended

190910 16:55:52 mysqld_safe Starting mysqld daemon with databases from /export/mysql/data/mysql

2019-09-10 16:55:53 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2019-09-10 16:55:53 0 [Note] /usr/local/mysql/mysql/bin/mysqld (mysqld 5.6.33-log) starting as process 33313 ...

/usr/local/mysql/mysql/bin/mysqld: File '/export/mysql/data.index' not found (Errcode: 13 - Permission denied)

# 数据存储目录权限问题

chown -R mysql:mysql /export/mysql

• start slave报错，开始同步不成功

show slave status \G; 

Slave_IO_Running: YesSlave_SQL_Running: No

2016-06-09 00:07:07 23352 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)

2016-06-09 00:07:07 23352 [Note] Slave I/O thread killed while reading event

2016-06-09 00:07:07 23352 [Note] Slave I/O thread exiting, read up to log 'mysql-bin-190.000667', position 9049889

2016-06-09 00:07:07 23352 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.

2016-06-09 00:07:07 23352 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0

2016-06-09 00:07:07 23352 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin-190.000666' at position 2935199, relay log './mysql3306-relay-bin.000002' position: 2935366

2016-06-09 00:07:07 23352 [Note] 'SQL_SLAVE_SKIP_COUNTER=1' executed at relay_log_file='./mysql3306-relay-bin.000002', relay_log_pos='2935366', master_log_name='mysql-bin-190.000666', master_log_pos='2935199' and new position at relay_log_file='./mysql3306-relay-bin.000002', relay_log_pos='2935750', master_log_name='mysql-bin-190.000666', master_log_pos='2935583' 

2016-06-09 00:07:07 23352 [Note] Slave I/O thread: connected to master '[email protected]:3306',replication started in log 'mysql-bin-190.000667' at position 9049889

2016-06-09 00:07:08 23352 [ERROR] Slave SQL: Could not execute Write_rows event on table asy.pm_camera_recordrate; Duplicate entry '913df478e36f4f888505874ddec59240' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin-190.000666, end_log_pos 2944382, Error_code: 1062

2016-06-09 00:07:08 23352 [Warning] Slave: Duplicate entry '913df478e36f4f888505874ddec59240' for key 'PRIMARY' Error_code: 1062

解决方式：在my.cnf文件中加入如下代码到[mysqld]。重启mysql

slave-skip-errors = 1062

如果能预估到错误数据比较少。也可以用如下代码。每次执行只跳过一个事务。

stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 ; start slave ;

跳过1062和1032错误 。可能会导致日志中出现警告信息。所以在在my.cnf文件中加入如下代码到[mysqld]。重启mysql可解决问题。

binlog_format=mixed

反思问题起因

• 在从库由于使用了 kill -9 杀掉了mysql线程。可能导致了mysql的事务回滚。也有可能导致了mysql中继日志出现问题。
• 在拉取备份文件恢复的时候。由于拉取了最新的备份数据。恢复数据的时候。只是重设了同步的binlog文件和pos点。并没有修改中继日志的起点。导致了中继日志中的数据应该是比数据库中的备份点数据更早了。然后产生了1062主键冲突和1032删除数据不存在的错误。