以下所有的实验都开启了二进制日志功能，并且实验的mariadb版本
vim /etc/my.cnf
[mysqld]
log_bin
binlog_format=row
innodb_file_per_table
实验1：主从复制
主节点配置：192.168.99.102
- 启用二进制日志
[centos]$ vim /etc/my.cnf
[mysqld]
server_id=1 #为当前节点设置一个全局惟一的ID号，整数即可
log_bin=/path #也可以不写路径
log-basename=master #可选项，设置datadir中日志名称，确保不依赖主机名
binlog_format=row
innodb_file_per_table
[centos]$ systemctl restart mariadb
- 创建有复制权限的用户账号
[centos]$ mysql
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' IDENTIFIED BY 'replpass';
- 完全备份到从服务器上
#完全备份
[centos]$ mysqldump -F -A --single-transaction --master-data=1 > all.sql
#将备份拷贝到从服务器上
[centos]$ scp all.sql 192.168.99.102:/root/
从节点配置：192.168.99.103
- 修改配置
[centos]$ vim /etc/my.cnf
[mysqld]
server_id=2 #为当前节点设置一个全局惟的ID号
read_only=ON #设置数据库只读
relay_log=relay-log #relay log的文件路径，默认值hostname-relay-bin
relay_log_index=relay-log.index #默认值hostname-relay-bin.index
[centos]$ systemctl restart mariadb
- 在使用备份之前，先看看这个备份
[centos]$ vim /root/all.sql
...
--
-- Position to start replication or point-in-time recovery from
--
CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000001',MASTER_LOG_POS=245;
#要连接主服务器，我们得改改这条，用来连接主服务器来同步
- 改成这样
[centos]$ vim /root/all.sql
...
--
-- Position to start replication or point-in-time recovery from
--
CHANGE MASTER TO
MASTER_HOST='192.168.99.102',
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE=' mariadb-bin.000001',
MASTER_LOG_POS=245;
- 启动
MariaDB [(none)]> START SLAVE;
- #查看从服务器运行状态
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.99.102
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: yourmaster-bin.000001
Read_Master_Log_Pos: 8269
Relay_Log_File: myrelay.000002
Relay_Log_Pos: 8558
......
- 查看线程
#第一行是SQL线程，第二行是IO线程
MariaDB [hellodb]> show full processlist\G;
*************************** 1. row ***************************
Id: 4
User: system user
Host:
db: NULL
Command: Connect
Time: 19230
State: Waiting for master to send event
Info: NULL
Progress: 0.000
*************************** 2. row ***************************
Id: 5
User: system user
Host:
db: NULL
Command: Connect
Time: 8436
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
Progress: 0.000
TIP：
1. 如果想实现一主多从，那从服务器按照上面的从服务器来配置就可以了。
2. 如果主从发生了错误，而又想忽略错误继续运行，可以
set global sql_slave_skip_counter=N
#N表示忽略N个错误
#注意，这个并不能解决问题，错误依旧存在，还需要解决
- 如果想跳过某个错误编号，可以
[centos]$ vim /etc/my.cnf
slave-skip-errors=1062
#1062是一个错误编号
实验2：主主复制
如果系统做过了其它实验，建议还原快照。
实验环境：
主1：192.168.99.102
主2：192.168.99.103
主1服务器 192.168.99.102
1. 配置文件修改
[centos]$ vim /etc/my.cnf
[mysqld]
server_id=1
log_bin
relay_log=relay-log #可选 relay log的文件路径，默认值hostname-relay-bin
relay_log_index=relay-log.index #可选 默认值hostname-relay-bin.index
auto_increment_offset=1
auto_increment_increment=2
...
- 重启服务
[centos]$ systemctl restart mariadb
- 创建有复制权限的用户账号
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repluser1'@'%' IDENTIFIED BY 'replpass';
主2服务器 192.168.99.103
1. 配置文件修改
[centos]$ vim /etc/my.cnf
[mysqld]
server_id=2
log_bin
relay_log=relay-log #可选 relay log的文件路径，默认值hostname-relay-bin
relay_log_index=relay-log.index #可选 默认值hostname-relay-bin.index
auto_increment_offset=2
auto_increment_increment=2
binlog_format=row #可选
innodb_file_per_table #可选
...
- 重启服务
[centos]$ systemctl restart mariadb
- 使用有复制权限的用户账号连接至主服务器，并启动复制线程
MariaDB [(none)]> CHANGE MASTER TO
MASTER_HOST='192.168.99.102',
MASTER_USER='repluser1',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE=' mariadb-bin.000001',
MASTER_LOG_POS=0;
#如果是从主服务器备份过来的，要把备份文件也这么改下。详见实验1：主从复制
- 创建有复制权限的用户账号
其实也不用创建，连接到上面的主服务器后就把上面的创建的帐号同步过来了
#可以看下
MariaDB [(none)]> select user,host from mysql.user;
+----------+-----------------------+
| user | host |
+----------+-----------------------+
| repluser | % |
| root | |
+----------+-----------------------+
主1服务器 192.168.99.102
注意这里是主1服务器
1. 使用有复制权限的用户账号连接至主2服务器，并启动复制线程
MariaDB [(none)]> CHANGE MASTER TO
MASTER_HOST='192.168.99.102',
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE=' mariadb-bin.xxxxxx',
MASTER_LOG_POS=0;
#如果从主服务器备份数据，MASTER_LOG_FILE后面跟的二进制文件就写备份的当前编号及POS
- 启动
MariaDB [(none)]> START SLAVE;
- 查看主1服务器运行状态
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.99.102
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: yourmaster-bin.000001
Read_Master_Log_Pos: 8269
Relay_Log_File: myrelay.000002
Relay_Log_Pos: 8558
......
- 如果启动失败，错误编号为1045
#在确认密码没有错误的情况下，可以使用下面的命令
MariaDB [(none)]> flush privileges;
主2服务器 192.168.99.103
注意这里是主2服务器
4. 启动主2
MariaDB [(none)]> START SLAVE;
- 查看主2服务器运行状态
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.99.102
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: yourmaster-bin.000001
Read_Master_Log_Pos: 8269
Relay_Log_File: myrelay.000002
Relay_Log_Pos: 8558
......
- 查看线程
#第一行是SQL线程，第二行是IO线程
MariaDB [hellodb]> show full processlist\G;
*************************** 1. row ***************************
Id: 4
User: system user
Host:
db: NULL
Command: Connect
Time: 19230
State: Waiting for master to send event
Info: NULL
Progress: 0.000
*************************** 2. row ***************************
Id: 5
User: system user
Host:
db: NULL
Command: Connect
Time: 8436
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
Progress: 0.000
附： 上面的配置里auto_increment是什么作用呢?
auto_increment_offset=1
auto_increment_increment=2
#就是防止同时写冲突用的，
MariaDB [test]> select * from a;
+----+------+
| id | name |
+----+------+
| 1 | wang |
| 3 | wang |
| 5 | wang |
+----+------+
下面是验证环节
主1
[centos]$ mysql < hellodb_innodb.sql
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
主2
跑到主2来看看数据库有了没
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
同样，我们在主2上也弄个数据库，这次我们手动来建一个
MariaDB [(none)]> create database m2;
MariaDB [(none)]> use m2
MariaDB [m2]> create table t2(id int primary key auto_increment,name varchar(20));
#插3条记录，看看
MariaDB [m2]> insert t2(name)value('chen');
MariaDB [m2]> insert t2(name)value('wang');
MariaDB [m2]> insert t2(name)value('zhao');
MariaDB [m2]> select * from t2;
+----+------+
| id | name |
+----+------+
| 2 | chen |
| 4 | wang |
| 6 | zhao |
+----+------+
主1
来主1看看有没有，顺便改改
MariaDB [(none)]> use m2
Database changed
MariaDB [m2]> select * from t2;
+----+------+
| id | name |
+----+------+
| 2 | chen |
| 4 | wang |
| 6 | zhao |
+----+------+
我们也插3条记录下去看看
MariaDB [m2]> insert t2(name)value('chen111');
MariaDB [m2]> insert t2(name)value('wang1111');
MariaDB [m2]> insert t2(name)value('zhao111');
MariaDB [m2]> select * from t2;
+----+----------+
| id | name |
+----+----------+
| 2 | chen |
| 4 | wang |
| 6 | zhao |
| 7 | chen111 |
| 9 | wang1111 |
| 11 | zhao111 |
+----+----------+
主2
最后来主2再看看
MariaDB [m2]> select * from t2;
+----+----------+
| id | name |
+----+----------+
| 2 | chen |
| 4 | wang |
| 6 | zhao |
| 7 | chen111 |
| 9 | wang1111 |
| 11 | zhao111 |
+----+----------+
实验3：级联复制
主1服务器 192.168.99.102
1. 配置文件修改
[centos]$ vim /etc/my.cnf
[mysqld]
server_id=1
log_bin
...
- 重启服务
[centos]$ systemctl restart mariadb
- 创建有复制权限的用户账号
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' IDENTIFIED BY 'replpass';
- 完全备份到从服务器上
#完全备份
[centos]$ mysqldump -F -A --single-transaction --master-data=1 > all.sql
#将备份拷贝到中间服务器上
[centos]$ scp all.sql 192.168.99.102:/root/
中间服务器
1. 配置文件修改
vim /etc/my.cnf
[mysqld]
server_id=2
log_slave_updates
log_bin
read_only
- 改成这样(在原本是啥样的可以看看上面的实验1：主从复制)
[centos]$ vim /root/all.sql
...
--
-- Position to start replication or point-in-time recovery from
--
CHANGE MASTER TO
MASTER_HOST='192.168.99.101',
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE=' mariadb-bin.000001',
MASTER_LOG_POS=245;
...
- 把中间服务器上完全备份到最后服务器上
#完全备份
[centos]$ mysqldump -F -A --single-transaction --master-data=1 > mid.sql
#将备份拷贝到最后服务器上
[centos]$ scp mid.sql 192.168.99.103:/root/
- 进入mysql启动slave
MariaDB [(none)]> start slave ;
#查看状态，没有错误就算连接成功了
MariaDB [(none)]> show slave status \G ;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.99.102
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000004
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000007
Relay_Log_Pos: 531
Relay_Master_Log_File: mariadb-bin.000004
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: 245
Relay_Log_Space: 1271
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: 2
1 row in set (0.00 sec)
最后服务器:192.168.99.103
1. 配置文件修改
vim /etc/my.ncf
[mysqld]
server_id=3
read_only
- 改成这样
[centos]$ vim /root/all.sql
...
--
-- Position to start replication or point-in-time recovery from
--
CHANGE MASTER TO
MASTER_HOST='192.168.99.102',
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE=' mariadb-bin.000001',
MASTER_LOG_POS=245;
- 进入mysql启动slave
MariaDB [(none)]> start slave ;
#查看状态，没有错误就算连接成功了
MariaDB [(none)]> show slave status \G ;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.99.102
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000004
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000007
Relay_Log_Pos: 531
Relay_Master_Log_File: mariadb-bin.000004
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: 245
Relay_Log_Space: 1271
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: 2
1 row in set (0.00 sec)
实验4：半同步复制
主服务器：192.168.99.101
1. 修改配置文件
[centos]$ vim /etc/my.cnf
[mysqld]
server_id=1
log_bin
- 重启服务
[centos]$ systemctl restart mariadb
- 创建帐号用于复制
MariaDB [(none)]> grant replication slave on *.* to repluser@'%' identified by '123';
从服务器1：192.168.99.102
1. 修改配置文件
[centos]$ vim /etc/my.cnf
[mysqld]
server_id=2
- 重启服务
[centos]$ systemctl restart mariadb
- 连接到主服务器
MariaDB [(none)]> change master to
master_host='192.168.99.101',
master_user='repluser',
master_password='123',
master_port=3306,
master_log_file='mariadb-bin.000001',
master_log_pos=0;
- 启动slave
MariaDB [(none)]> start slave ;
- 查看是否连接上
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.99.101
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 386
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 672
Relay_Master_Log_File: mariadb-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: 386
Relay_Log_Space: 968
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
从服务器2：192.168.99.103
1. 修改配置文件
[centos]$ vim /etc/my.cnf
[mysqld]
server_id=3
- 重启服务
[centos]$ systemctl restart mariadb
- 连接到主服务器
MariaDB [(none)]> change master to
master_host='192.168.99.101',
master_user='repluser',
master_password='123',
master_port=3306,
master_log_file='mariadb-bin.000001',
master_log_pos=0;
- 启动slave
MariaDB [(none)]> start slave ;
- 查看是否启动成功
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.99.101
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 386
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 672
Relay_Master_Log_File: mariadb-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: 386
Relay_Log_Space: 968
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
主服务器：192.168.99.101
1. 在主服务器上安装主服务器的插件
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
- 查看安装的插件
MariaDB [(none)]> show plugins;
...
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
+--------------------------------+----------+--------------------+--------------------+---------+
- 查看下这个插件的状态，并没有启动，enabled为OFF
MariaDB [(none)]> show global variables like '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
#其中：timeout这个参数是指同步超时时间
- 启用这个插件
MariaDB [(none)]> set global rpl_semi_sync_master_enabled=on;
- 查看下，是不是启用了
MariaDB [(none)]> show global variables like '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
...
从服务器1：192.168.99.102
1. 从服务器安装的插件不太一样，看
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
- 查看安装的插件
MariaDB [(none)]> show plugins;
| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |
+--------------------------------+----------+--------------------+-------------------+---------+
- 查看下这个插件的状态，并没有启动，enabled为OFF
MariaDB [(none)]> show global variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
- 启用它
MariaDB [(none)]> set global rpl_semi_sync_slave_enabled=on;
- 看，启动了
MariaDB [(none)]> show global variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
- 设置完还得重启线程
MariaDB [(none)]> stop slave;
MariaDB [(none)]> start slave;
从服务器2：192.168.99.103
1. 从服务器安装的插件不太一样，看
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
- 查看安装的插件
MariaDB [(none)]> show plugins;
| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |
+--------------------------------+----------+--------------------+-------------------+---------+
- 查看下这个插件的状态，并没有启动，enabled为OFF
MariaDB [(none)]> show global variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
- 启用它
MariaDB [(none)]> set global rpl_semi_sync_slave_enabled=on;
- 看，启动了
MariaDB [(none)]> show global variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
- 设置完还得重启线程
MariaDB [(none)]> stop slave;
MariaDB [(none)]> start slave;
主服务器：192.168.99.101
1. 回到主服务器上看，已经有2个线程了
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 2 |
...
实验5：基于SSL加密复制
实验前先还原快照，把其它实验清除了
实验环境：
主服务器：192.168.99.101
从服务器：192.168.99.102主服务器：192.168.99.101
1. 建立SSL目录来存放证书
[centos]$ mkdir /etc/my.cnf.d/ssl
[centos]$ cd /etc/my.cnf.d/ssl
- 生成私钥
[centos]$ openssl genrsa 2048 > cakey.pem
- 生成CA证书
[centos]$ openssl req -new -x509 -key cakey.pem -out cacert.pem -days 999
...
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:beijing
Locality Name (eg, city) [Default City]:beijing
Organization Name (eg, company) [Default Company Ltd]:magedu
Organizational Unit Name (eg, section) []:devops
Common Name (eg, your name or your server's hostname) []:ca.magedu.com
Email Address []:
这里是CA和主服务器是一台，所以就都在同一台机上做
4. 生成证书申请
[centos]$ openssl req -newkey rsa:1024 -days 888 -nodes -keyout master.key > master.csr
...
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:beijing
Locality Name (eg, city) [Default City]:beijing
Organization Name (eg, company) [Default Company Ltd]:magedu
Organizational Unit Name (eg, section) []:devops
Common Name (eg, your name or your server's hostname) []:master.magedu.com
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
- 给主服务器颁发证书
[centos]$ openssl x509 -req -in master.csr -days 777 -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt
Signature ok
subject=/C=CN/ST=beijing/L=beijing/O=magedu/OU=devops/CN=master.magedu.com
Getting CA Private Key
- 给从服务器生成证书申请
[centos]$ openssl req -newkey rsa:1024 -days 888 -nodes -keyout slave.key > slave.csr
...
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:beijing
Locality Name (eg, city) [Default City]:beijing
Organization Name (eg, company) [Default Company Ltd]:magedu
Organizational Unit Name (eg, section) []:devops
Common Name (eg, your name or your server's hostname) []:slave.magedu.com
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
- 给从服务器生成证书
[centos]$ openssl x509 -req -in slave.csr -days 777 -CA cacert.pem -CAkey cakey.pem -set_serial 02 > slave.crt
Signature ok
subject=/C=CN/ST=beijing/L=beijing/O=magedu/OU=devops/CN=slave.magedu.com
Getting CA Private Key
- 导入证书，也就是修改配置文件
[centos]$ vim /etc/my.cnf
[mysqld]
server_id=1
log_bin
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/master.crt
ssl-key=/etc/my.cnf.d/ssl/master.key
- 重启服务
[centos]$ systemctl restart mariadb
- 查看下导入后的状态
MariaDB [(none)]> show variables like '%ssl%';
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /etc/my.cnf.d/ssl/cacert.pem |
| ssl_capath | |
| ssl_cert | /etc/my.cnf.d/ssl/master.crt |
| ssl_cipher | |
| ssl_key | /etc/my.cnf.d/ssl/master.key |
+---------------+------------------------------+
- 使用SSL来连接本机吧
[centos]$ cd /etc/my.cnf.d/ssl
[centos]$ mysql --ssl-ca=cacert.pem --ssl-cert=master.crt --ssl-key=master.key
- 看
SSL这行，表明已经用SSL加密来登录了
MariaDB [(none)]> status;
--------------
mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 4
Current database:
Current user: root@localhost
SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384
- 把证书发给从节点
[centos]$ scp cacert.pem slave.key slave.crt 192.168.99.102:/etc/my.cnf.d/ssl
- 创建强制要求ssl登录的帐号
[centos]$ mysql
MariaDB [(none)]> grant replication slave on *.* to repluser2@'%' identified by 'centos' require ssl;
从服务器：192.168.99.102
注意啦，我们现在在从服务器上
[centos]$ cd /etc/my.cnf.d/ssl [centos]$ mysql --ssl-ca=cacert.pem --ssl-cert=slave.crt --ssl-key=slave.key -h 192.168.99.101 -urepluser -pcentosBashCopy- 把发来的证书写到配置文件里
[centos]$ vim /etc/my.cnf
[mysqld]
server_id=2
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/slave.crt
ssl-key=/etc/my.cnf.d/ssl/slave.key
- 把原来的连接断开
MariaDB [(none)]> stop slave;
MariaDB [(none)]> reset slave all;
- 连接主服务器
MariaDB [(none)]> CHANGE MASTER TO
MASTER_HOST='192.168.99.101',
MASTER_USER='repluser2',
MASTER_PASSWORD='centos',
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=245,
MASTER_SSL=1;
注意，如果证书没有写到配置文件时，还可以这样来连接主服务器
MariaDB [(none)]> CHANGE MASTER TO
MASTER_HOST='192.168.99.101',
MASTER_USER='repluser2',
MASTER_PASSWORD='centos',
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=245,
MASTER_SSL=1,
MASTER_SSL_CA = '/etc/my.cnf.d/ssl/cacert.pem',
MASTER_SSL_CERT = '/etc/my.cnf.d/ssl/slave.crt',
MASTER_SSL_KEY = '/etc/my.cnf.d/ssl/slave.key';
- 启动
MariaDB [(none)]> start slave;
- 查看连接的状态
MariaDB [m1]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.99.101
Master_User: repluser2
Master_Port: 3306
... .... ...
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File:
Master_SSL_CA_Path:
... ...
#master_ssl_allowed已经显示yes就说明连接上了