mysql5.5 ssl_MySQL 5.5 到MySQL 5.6半同步复制（SSL）

最新推荐文章于 2023-02-02 15:45:13 发布

大包子狸

最新推荐文章于 2023-02-02 15:45:13 发布

阅读量195

点赞数

文章标签： mysql5.5 ssl

本文链接：https://blog.csdn.net/weixin_42550750/article/details/113403039

版权

数据放在一个raid 1上：

[root@slave1 ~]# yum -y install mdadm

[root@slave1 ~]# mdadm -C /dev/md0 -l1 -n2 /dev/sd{b,c}

[root@slave1 ~]# cat /proc/mdstat

Personalities : [raid1]

md0 : active raid1 sdc[1] sdb[0]

5241844 blocks super 1.2 [2/2] [UU]

[====>................] resync = 23.0% (1207808/5241844) finish=3.6min speed=18452K/sec

unused devices:

[root@slave1 ~]# mdadm -D /dev/md0

/dev/md0:

Version : 1.2

Creation Time : Mon Aug 19 18:51:05 2013

Raid Level : raid1

Array Size : 5241844 (5.00 GiB 5.37 GB)

Used Dev Size : 5241844 (5.00 GiB 5.37 GB)

Raid Devices : 2

Total Devices : 2

Persistence : Superblock is persistent

Update Time : Mon Aug 19 18:56:02 2013

State : clean

Active Devices : 2

Working Devices : 2

Failed Devices : 0

Spare Devices : 0

Name : slave1:0 (local to host slave1)

UUID : 88d2d04d:a886df3f:2c2a8a10:247becc5

Events : 17

Number Major Minor RaidDevice State

0 8 16 0 active sync /dev/sdb

1 8 32 1 active sync /dev/sdc

[root@slave1 ~]# mdadm -Ds >> /etc/mdadm.conf

[root@slave1 ~]# cat /etc/mdadm.conf

ARRAY /dev/md0 metadata=1.2 name=slave1:0 UUID=88d2d04d:a886df3f:2c2a8a10:247becc5

[root@slave1 ~]# mkdir /mydata

[root@slave1 ~]# echo "` blkid /dev/md0 | awk '{print $2}'` /mydata/ ext4 defaults 0 0 " >>/etc/fstab

[root@slave1 ~]# mount -a

[root@slave1 ~]# df -PTh | grep md0

/dev/md0 ext4 5.0G 138M 4.6G 3% /mydata

[root@slave1 ~]# yum -y install libaio-devel

[root@slave1 ~]# tar xvf mysql-5.6.10-linux-glibc2.5-i686.tar.gz -C /usr/local/

[root@slave1 ~]# mkdir /mydata/data

[root@slave1 ~]# groupadd -r mysql

[root@slave1 ~]# useradd -g mysql -r -s /sbin/nologin -M -d /mydata/data mysql

[root@slave1 ~]# chown -R mysql:mysql /mydata/data

[root@slave1 ~]# cd /usr/local/

[root@slave1 local]# ln -sv mysql-5.6.10-linux-glibc2.5-i686/ mysql

`mysql' -> `mysql-5.6.10-linux-glibc2.5-i686/'

[root@slave1 local]# chown -R mysql:mysql mysql/*

root@slave1 local]# cd mysql

[root@slave1 mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data

[root@slave1 mysql]# chown -R root .

[root@slave1 mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld

[root@slave1 mysql]# chmod +x /etc/rc.d/init.d/mysqld

[root@slave1 mysql]# chkconfig --add mysqld

[root@slave1 mysql]# ln -sv /usr/local/mysql/bin/mysql /bin/

`/bin/mysql' -> `/usr/local/mysql/bin/mysql'

[root@slave1 mysql]# ln -sv /usr/local/mysql/include /usr/include/mysql

`/usr/include/mysql' -> `/usr/local/mysql/include'

[root@slave1 mysql]# echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf && ldconfig

[root@master ~]# cp /etc/my.cnf master.cnf

[root@master ~]# cp master.cnf slave.conf

[root@master ~]# vim slave.conf

[root@master ~]# !diff

diff master.cnf slave.conf

54c54

< log-bin=mysql-bin

---

> #log-bin=mysql-bin

57c57

< binlog_format=mixed

---

> #binlog_format=mixed

62c62,63

< server-id = 1

---

> server-id = 11

> replicate_do_db = shop

[root@master ~]# scp slave.conf 10.10.10.62:/etc/my.cnf

[root@slave1 mysql]# service mysqld start

Starting MySQL.... SUCCESS!

[root@slave1 ~]# mysql -e "select version();show databases;"

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

| version() |

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

| 5.6.10-log |

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

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

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

master上授权复制用户

mysql> GRANT REPLICATION SLAVE ON *.* TO repluser@'10.10.10.%' IDENTIFIED BY 'redhat';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.07 sec)

准备数据：

[root@master ~]# mysqldump -p`cat abc` --master-data=2 shop >shop.sql

[root@master ~]# scp shop.sql 10.10.10.62:/root/

这里以注释的方式记下了二进制日志位置，作为后面参考

[root@slave1 ~]# grep -i master shop.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=490;

[root@slave1 ~]# mysql -e "create database shop;"

[root@slave1 ~]# mysql shop

mysql> CHANGE MASTER TO

-> MASTER_HOST='10.10.10.61',

-> MASTER_USER='repluser',

-> MASTER_PASSWORD='redhat',

-> MASTER_PORT=3306,

-> MASTER_LOG_FILE='mysql-bin.000006',

-> MASTER_LOG_POS=490,

-> MASTER_CONNECT_RETRY=10;

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

mysql> show warnings\G

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

Level: Note

Code: 1759

Message: Sending passwords in plain text without SSL/TLS is extremely insecure.

*************************** 2. row ***************************

Level: Note

Code: 1760

Message: Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.

2 rows in set (0.03 sec)

mysql> start slave;

Query OK, 0 rows affected (0.06 sec)

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 10.10.10.61

Master_User: repluser

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 490

Relay_Log_File: slave1-relay-bin.000002

Relay_Log_Pos: 270

Relay_Master_Log_File: mysql-bin.000006

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: shop

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

Relay_Log_Space: 444

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:

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

1 row in set (0.00 sec)

[root@slave1 ~]# mysql -e 'show databases;select user_id,user_name,last_time from shop.ecs_users;'

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| shop |

| test |

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

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

| user_id | user_name | last_time |

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

| 1 | ecshop | 0000-00-00 00:00:00 |

| 2 | vip | 0000-00-00 00:00:00 |

| 3 | text | 0000-00-00 00:00:00 |

| 5 | zuanshi | 0000-00-00 00:00:00 |

| 8 | week1 | 2013-08-19 00:00:14 |

| 9 | week2 | 2013-08-20 00:00:06 |

| 10 | week3 | 2013-08-21 00:00:08 |

| 11 | week4 | 2013-08-22 00:00:06 |

| 12 | week5 | 2013-08-23 00:00:03 |

| 13 | week6 | 2013-08-24 00:00:03 |

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

master上插入一条记录：

Master> insert into shop.ecs_users(user_name,last_time) values('week0',now());

Master> select user_id,user_name,last_time from shop.ecs_users;

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

| user_id | user_name | last_time |

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

| 1 | ecshop | 0000-00-00 00:00:00 |

| 2 | vip | 0000-00-00 00:00:00 |

| 3 | text | 0000-00-00 00:00:00 |

| 5 | zuanshi | 0000-00-00 00:00:00 |

| 8 | week1 | 2013-08-19 00:00:14 |

| 9 | week2 | 2013-08-20 00:00:06 |

| 10 | week3 | 2013-08-21 00:00:08 |

| 11 | week4 | 2013-08-22 00:00:06 |

| 12 | week5 | 2013-08-23 00:00:03 |

| 13 | week6 | 2013-08-24 00:00:03 |

| 16 | week0 | 2013-08-25 15:33:53 |

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

11 rows in set (0.00 sec)

从库上查询

[root@slave1 ~]# mysql -e 'select user_id,user_name,last_time from shop.ecs_users;'

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

| user_id | user_name | last_time |

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

| 1 | ecshop | 0000-00-00 00:00:00 |

| 2 | vip | 0000-00-00 00:00:00 |

| 3 | text | 0000-00-00 00:00:00 |

| 5 | zuanshi | 0000-00-00 00:00:00 |

| 8 | week1 | 2013-08-19 00:00:14 |

| 9 | week2 | 2013-08-20 00:00:06 |

| 10 | week3 | 2013-08-21 00:00:08 |

| 11 | week4 | 2013-08-22 00:00:06 |

| 12 | week5 | 2013-08-23 00:00:03 |

| 13 | week6 | 2013-08-24 00:00:03 |

| 16 | week0 | 2013-08-25 15:33:53 |

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

Slave1> use test

Database changed

Slave1> show tables;

Empty set (0.00 sec)

可见从库并没有复制Replicate_Do_DB之外的库

binlog_format=mixd不能正确复制的问题;

[root@master ~]# mysql -predhat -e "insert into shop.ecs_users(user_name,last_time) values('week`date +%w`',now());"

[root@master ~]# mysql -predhat -e 'select user_id,user_name,last_time from shop.ecs_users;'

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

| user_id | user_name | last_time |

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

| 1 | ecshop | 0000-00-00 00:00:00 |

| 2 | vip | 0000-00-00 00:00:00 |

| 3 | text | 0000-00-00 00:00:00 |

| 5 | zuanshi | 0000-00-00 00:00:00 |

| 8 | week1 | 2013-08-19 00:00:14 |

| 9 | week2 | 2013-08-20 00:00:06 |

| 10 | week3 | 2013-08-21 00:00:08 |

| 11 | week4 | 2013-08-22 00:00:06 |

| 12 | week5 | 2013-08-23 00:00:03 |

| 13 | week6 | 2013-08-24 00:00:03 |

| 19 | week0 | 2013-08-25 16:01:21 |

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

[root@master ~]# mysql -predhat -e 'show master status;'

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

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

| mysql-bin.000007 | 370 | | |

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

[root@slave1 ~]# mysql -e 'select user_id,user_name,last_time from shop.ecs_users;'

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

| user_id | user_name | last_time |

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

| 1 | ecshop | 0000-00-00 00:00:00 |

| 2 | vip | 0000-00-00 00:00:00 |

| 3 | text | 0000-00-00 00:00:00 |

| 5 | zuanshi | 0000-00-00 00:00:00 |

| 8 | week1 | 2013-08-19 00:00:14 |

| 9 | week2 | 2013-08-20 00:00:06 |

| 10 | week3 | 2013-08-21 00:00:08 |

| 11 | week4 | 2013-08-22 00:00:06 |

| 12 | week5 | 2013-08-23 00:00:03 |

| 13 | week6 | 2013-08-24 00:00:03 |

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

[root@slave1 ~]# mysql -e 'show slave status\G'

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

Slave_IO_State: Waiting for master to send event

Master_Host: 10.10.10.61

Master_User: repluser

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: mysql-bin.000007

Read_Master_Log_Pos: 370

Relay_Log_File: slave1-relay-bin.000004

Relay_Log_Pos: 529

Relay_Master_Log_File: mysql-bin.000007

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: shop

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

Relay_Log_Space: 742

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:

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

解决办法：

[root@master ~]# vim /etc/my.cnf

binlog_format=row

[root@master ~]# service mysqld restart

Shutting down MySQL.... SUCCESS!

Starting MySQL.... SUCCESS!

[root@master ~]# mysql -predhat -e "insert into shop.ecs_users(user_name,last_time) values('week10',now());"

[root@master ~]# mysql -predhat -e 'select user_id,user_name,last_time from shop.ecs_users;'

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

| user_id | user_name | last_time |

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

| 1 | ecshop | 0000-00-00 00:00:00 |

| 2 | vip | 0000-00-00 00:00:00 |

| 3 | text | 0000-00-00 00:00:00 |

| 5 | zuanshi | 0000-00-00 00:00:00 |

| 8 | week1 | 2013-08-19 00:00:14 |

| 9 | week2 | 2013-08-20 00:00:06 |

| 10 | week3 | 2013-08-21 00:00:08 |

| 11 | week4 | 2013-08-22 00:00:06 |

| 12 | week5 | 2013-08-23 00:00:03 |

| 13 | week6 | 2013-08-24 00:00:03 |

| 19 | week0 | 2013-08-25 16:01:21 |

| 20 | week10 | 2013-08-25 16:06:59 |

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

MySQL 5.5 -> MySQL 5.6半同步复制：

安装插件

Master> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

Slave1> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

修改配置文件：

master上

[root@master ~]# vim /etc/my.cnf

rpl_semi_sync_master_enabled=ON

slave 上

[root@slave1 ~]# vim /etc/my.cnf

rpl_semi_sync_slave_enabled=ON

重新读取配置文件

[root@master ~]# service mysqld reload

[root@slave1 ~]# service mysqld reload

[root@master ~]# mysql -p`cat abc` -e "show global variables like 'rpl_%';"

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

| Variable_name | Value |

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

| rpl_recovery_rank | 0 |

| rpl_semi_sync_master_enabled | ON |

| rpl_semi_sync_master_timeout | 10000 |

| rpl_semi_sync_master_trace_level | 32 |

| rpl_semi_sync_master_wait_no_slave | ON |

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

[root@master ~]# mysql -p`cat abc` -e "show global status like 'rpl_%status';"

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

| Variable_name | Value |

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

| Rpl_semi_sync_master_status | ON |

| Rpl_status | AUTH_MASTER |

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

[root@slave1 ~]# mysql -e "show global variables like '%rpl%';"

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

| Variable_name | Value |

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

| rpl_semi_sync_slave_enabled | ON |

| rpl_semi_sync_slave_trace_level | 32 |

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

[root@slave1 ~]# mysql -e "show global status like 'rpl%';"

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

| Variable_name | Value |

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

| Rpl_semi_sync_slave_status | ON |

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

基于ssl的复制

主服务器上配置CA

修改openssl.cnf配置文件

[root@master ~]# cp /etc/pki/tls/openssl.cnf /etc/pki/tls/openssl.cnf.bak

[root@master ~]# sed -i "s/countryName_default.*XX/countryName_default\t\t= CN/g" /etc/pki/tls/openssl.cnf

[root@master ~]# sed -i "s/#stateOrProvinceName_default.*Province/stateOrProvinceName_default\t= shanghai/g" /etc/pki/tls/openssl.cnf

[root@master ~]# sed -i "s/localityName_default.*City/localityName_default\t= shanghai/g" /etc/pki/tls/openssl.cnf

[root@master ~]# sed -i "s/0.organizationName_default.*/0.organizationName_default\t= SanYu/g" /etc/pki/tls/openssl.cnf

[root@master ~]# sed -i "s/#organizationalUnitName_default.*=/organizationalUnitName_default\t= Tech/g" /etc/pki/tls/openssl.cnf

[root@master ~]# (umask 077 ;openssl genrsa -out /etc/pki/CA/private/cakey.pem 2048)

Generating RSA private key, 2048 bit long modulus

......................................................................................................................+++

........+++

e is 65537 (0x10001)

[root@master ~]# openssl req -new -x509 -key /etc/pki/CA/private/cakey.pem -out /etc/pki/CA/cacert.pem

You are about to be asked to enter information that will be incorporated

into your certificate request.

What you are about to enter is what is called a Distinguished Name or a DN.

There are quite a few fields but you can leave some blank

For some fields there will be a default value,

If you enter '.', the field will be left blank.

-----

Country Name (2 letter code) [CN]:

State or Province Name (full name) [shanghai]:

Locality Name (eg, city) [shanghai]:

Organization Name (eg, company) [SanYu]:

Organizational Unit Name (eg, section) [Tech]:

Common Name (eg, your name or your server's hostname) []:master.sanyu.com

Email Address []:root@sanyu.com

[root@master ~]# touch /etc/pki/CA/index.txt

[root@master ~]# touch /etc/pki/CA/serial

[root@master ~]# echo 01 >> /etc/pki/CA/serial

[root@master ~]# mkdir /usr/local/mysql/ssl

[root@master ~]# cd /usr/local/mysql/ssl/

[root@master ssl]# openssl req -new -key mysql.key -out mysql.csr

[root@master ~]# (umask 077;openssl genrsa -out /usr/local/mysql/ssl/mysql.key 1024)

[root@master ssl]# openssl ca -in mysql.csr -out mysql.crt -days 365

[root@master ssl]# chown -R mysql.mysql ../ssl/

[root@master ssl]# ll

total 16

-rw-r--r-- 1 mysql mysql 1424 Aug 25 18:24 cacert.pem

-rw-r--r-- 1 mysql mysql 3853 Aug 25 18:24 mysql.crt

-rw-r--r-- 1 mysql mysql 700 Aug 25 18:23 mysql.csr

-rw------- 1 mysql mysql 887 Aug 25 18:19 mysql.key

从服务器上：

[root@slave1 ~]# mkdir /usr/local/mysql/ssl

[root@slave1 ~]# cd !$

cd /usr/local/mysql/ssl

[root@slave1 ssl]# (umask 077;openssl genrsa -out mysql.key 1024)

[root@slave1 ssl]# openssl req -new -key mysql.key -out mysql.csr

[root@slave1 ssl]# scp mysql.csr 10.10.10.61:/root/

主服务器上CA为从服务器颁发证书

[root@master ~]# openssl ca -in mysql.csr -out mysql.crt

Using configuration from /etc/pki/tls/openssl.cnf

Check that the request matches the signature

Signature ok

Certificate Details:

Serial Number: 2 (0x2)

Validity

Not Before: Aug 25 10:31:59 2013 GMT

Not After : Aug 25 10:31:59 2014 GMT

Subject:

countryName = CN

stateOrProvinceName = shanghai

organizationName = SanYu

organizationalUnitName = Tech

commonName = slave1.sanyu.com

emailAddress = slave@sanyu.com

X509v3 extensions:

X509v3 Basic Constraints:

CA:FALSE

Netscape Comment:

OpenSSL Generated Certificate

X509v3 Subject Key Identifier:

06:E8:D4:87:31:21:DF:38:CE:01:42:D7:7E:16:D3:2A:4D:94:0C:1C

X509v3 Authority Key Identifier:

keyid:41:8B:3D:9A:D6:7E:63:A3:10:53:64:B4:B2:D9:E1:ED:3C:5E:5B:96

Certificate is to be certified until Aug 25 10:31:59 2014 GMT (365 days)

Sign the certificate? [y/n]:y

1 out of 1 certificate requests certified, commit? [y/n]y

Write out database with 1 new entries

Data Base Updated

[root@master ~]# scp mysql.crt /etc/pki/CA/cacert.pem 10.10.10.62:/usr/local/mysql/ssl/

[root@slave1 ssl]# chown -R mysql.mysql ../ssl/

修改my.conf启用ssl

[root@master ~]# grep ssl /etc/my.cnf

ssl-ca=/usr/local/mysql/ssl/cacert.pem

ssl-key=/usr/local/mysql/ssl/mysql.key

ssl-cert=/usr/local/mysql/ssl/mysql.crt

[root@slave1 ssl]# grep ssl /etc/my.cnf

ssl-ca=/usr/local/mysql/ssl/cacert.pem

ssl-key=/usr/local/mysql/ssl/mysql.key

ssl-cert=/usr/local/mysql/ssl/mysql.crt

[root@master ssl]# service mysqld restart

[root@slave1 ssl]# service mysqld restart

mysql> show variables like '%ssl%';

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

| Variable_name | Value |

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

| have_openssl | YES |

| have_ssl | YES |

| ssl_ca | /usr/local/mysql/ssl/cacert.pem |

| ssl_capath | |

| ssl_cert | /usr/local/mysql/ssl/mysql.crt |

| ssl_cipher | |

| ssl_key | /usr/local/mysql/ssl/mysql.key |

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

重新授权复制用户

Master>revoke REPLICATION SLAVE ON *.* from 'repluser'@'10.10.10.%';

Master> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'10.10.10.%' IDENTIFIED BY 'redhat' require ssl;

Master> flush privileges;

Slave1> CHANGE MASTER TO

-> MASTER_HOST='10.10.10.61',

-> MASTER_USER='repluser',

-> MASTER_PASSWORD='redhat',

-> MASTER_LOG_FILE='mysql-bin.000017',

-> MASTER_LOG_POS=484,

-> MASTER_SSL=1,

-> MASTER_SSL_CA='/usr/local/mysql/ssl/cacert.pem',

-> MASTER_SSL_CAPATH='/usr/local/mysql/ssl',

-> MASTER_SSL_CERT='/usr/local/mysql/ssl/mysql.crt',

-> MASTER_SSL_KEY='/usr/local/mysql/ssl/mysql.key';

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

Slave1> start slave;

Query OK, 0 rows affected (0.07 sec)

Slave1> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 10.10.10.61

Master_User: repluser

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: mysql-bin.000017

Read_Master_Log_Pos: 484

Relay_Log_File: slave1-relay-bin.000002

Relay_Log_Pos: 266

Relay_Master_Log_File: mysql-bin.000017

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: shop

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

Relay_Log_Space: 436

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: Yes

Master_SSL_CA_File: /usr/local/mysql/ssl/cacert.pem

Master_SSL_CA_Path: /usr/local/mysql/ssl

Master_SSL_Cert: /usr/local/mysql/ssl/mysql.crt

Master_SSL_Cipher:

Master_SSL_Key: /usr/local/mysql/ssl/mysql.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:

Master_Info_File: /mydata/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: /usr/local/mysql/ssl/cacert.pem

Master_SSL_Crlpath: /usr/local/mysql/ssl

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.01 sec)

主从数据不一致导致复制中止如何跳过错误

由于某些原因主库上某些记录从服务器上没有，主库上对这些数据做操作时从库不能正确执行，如果影响不大可以直接跳过错误继续执行

mysql> show slave status \G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 10.10.10.61

Master_User: repluser

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: mysql-bin.000015

Read_Master_Log_Pos: 1485

Relay_Log_File: slave1-relay-bin.000025

Relay_Log_Pos: 270

Relay_Master_Log_File: mysql-bin.000015

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB: shop

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1032

Last_Error: Could not execute Delete_rows_v1 event on table shop.ecs_users; Can't find record in 'ecs_users', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000015, end_log_pos 1255

Skip_Counter: 0

Exec_Master_Log_Pos: 107

Relay_Log_Space: 1822

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

Last_SQL_Error: Could not execute Delete_rows_v1 event on table shop.ecs_users; Can't find record in 'ecs_users', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000015, end_log_pos 1255

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID:

Master_Info_File: /mydata/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: 130826 09:58:50

Master_SSL_Crl: