|
1
2
3
4
5
6
7
|
######NOD1节点执行
sed
-i
's@\(HOSTNAME=\).*@\1master.allen.com@g'
/etc/sysconfig/network
hostname
master.allen.com
######NOD2节点执行
sed
-i
's@\(HOSTNAME=\).*@\1slave.allen.com@g'
/etc/sysconfig/network
hostname
slave.allen.com
注释：修改文件须重启系统生效，这里使用
"hostname"
命令先修改文件然后执行命令修改主机名称可以不用重启
|
|
1
2
3
4
5
|
######在两台服务器执行如下命令
cat
>>
/etc/hosts
<< EOF
172.16.14.1 master.allen.com master
172.16.14.2 slave.allen.com slave
EOF
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
######在Master服务器上安装Mysql
====================================================================
######添加Mysqld运行用户
[root@master ~]
# useradd -r -u 300 mysql
######创建数据存放目录
[root@master ~]
# mkdir -p /mydata/data
######解压并创建软链接
[root@master ~]
# tar xf mysql-5.5.33-linux2.6-x86_64.tar.gz -C /usr/local/
[root@master ~]
# cd /usr/local/
[root@master
local
]
# ln -s mysql-5.5.33-linux2.6-x86_64 mysql
[root@master
local
]
# cd mysql
######为Mysqld服务提供Sysv服务脚本并添加到系统服务设置为开机自启动
[root@master mysql]
# cp support-files/mysql.server /etc/init.d/mysqld
[root@master mysql]
# chmod +x /etc/init.d/mysqld
[root@master mysql]
# chkconfig --add mysqld
[root@master mysql]
# chkconfig mysqld on
######为Mysqld服务提供主配置文件
[root@master mysql]
# cp support-files/my-large.cnf /etc/my.cnf
######修改主配置文件添加以下选项
[root@master mysql]
# vim /etc/my.cnf
datadir =
/mydata/data
#数据存放目录
innodb_file_per_table = 1
#innodb表每表一个表空间
######修改PATH变量
[root@master mysql]
# echo "PATH=/usr/local/mysql/bin:$PATH" >> /etc/profile
[root@master mysql]
# . /etc/profile
######修改Mysqld服务的头文件让系统可以识别
[root@master mysql]
# ln -s /usr/local/mysql/include /usr/include/mysql
######修改Mysqld服务的库文件让系统可以识别
[root@master mysql]
# echo "/usr/local/mysql/lib" >> /etc/ld.so.conf
[root@master mysql]
# ldconfig
######设置Mysqld服务的安装程序与数据存放目录属主、属组用户
[root@master mysql]
# chown -R root.mysql ./*
[root@master mysql]
# chown -R mysql.mysql /mydata/data
######初始化数据库
[root@master mysql]
# ./scripts/mysql_install_db --user=mysql --datadir=/mydata/data/
######启动Mysqld服务测试
[root@master ~]
# service mysqld start
Starting MySQL.... [ OK ]
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
######在Slave服务器上安装Mysql
====================================================================
######添加Mysqld运行用户
[root@slave ~]
# useradd -r -u 300 mysql
######创建数据存放目录
[root@slave ~]
# mkdir -p /mydata/data
######解压并创建软链接
[root@slave ~]
# tar xf mysql-5.5.33-linux2.6-x86_64.tar.gz -C /usr/local/
[root@slave ~]
# cd /usr/local/
[root@slave
local
]
# ln -s mysql-5.5.33-linux2.6-x86_64 mysql
[root@slave
local
]
# cd mysql
######为Mysqld服务提供Sysv服务脚本并添加到系统服务设置为开机自启动
[root@slave mysql]
# cp support-files/mysql.server /etc/init.d/mysqld
[root@slave mysql]
# chmod +x /etc/init.d/mysqld
[root@slave mysql]
# chkconfig --add mysqld
[root@slave mysql]
# chkconfig mysqld on
######为Mysqld服务提供主配置文件
[root@slave mysql]
# cp support-files/my-large.cnf /etc/my.cnf
######修改主配置文件添加以下选项
[root@slave mysql]
# vim /etc/my.cnf
datadir =
/mydata/data
#数据存放目录
innodb_file_per_table = 1
#innodb表每表一个表空间
######修改PATH变量
[root@slave mysql]
# echo "PATH=/usr/local/mysql/bin:$PATH" >> /etc/profile
[root@slave mysql]
# . /etc/profile
######修改Mysqld服务的头文件让系统可以识别
[root@slave mysql]
# ln -s /usr/local/mysql/include /usr/include/mysql
######修改Mysqld服务的库文件让系统可以识别
[root@slave mysql]
# echo "/usr/local/mysql/lib" >> /etc/ld.so.conf
[root@slave mysql]
# ldconfig
######设置Mysqld服务的安装程序与数据存放目录属主、属组用户
[root@slave mysql]
# chown -R root.mysql ./*
[root@slave mysql]
# chown -R mysql.mysql /mydata/data
######初始化数据库
[root@slave mysql]
# ./scripts/mysql_install_db --user=mysql --datadir=/mydata/data/
######启动Mysqld服务测试
[root@slave ~]
# service mysqld start
Starting MySQL.... [ OK ]
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
[root@master ~]
# mysql
mysql> grant replication slave,replication client
on *.* to
'allen'
@
'172.16.14.2'
identified by
'p@ssword'
;
Query OK, 0 rows affected (0.02 sec);
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants
for
'allen'
@
'172.16.14.2'
;
#查看用户授权
+------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants
for
allen@172.16.14.2 |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO
'allen'
@
'172.16.14.2'
IDENTIFIED BY PASSWORD
'*4F477FE814A0E3A4A5FD42BBB87C2DE8C36750DE'
|
+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row
in
set
(0.01 sec)
|
|
1
2
3
4
5
6
7
8
9
10
|
[root@slave ~]
# mysql -uallen -pp@ssword -h 172.16.14.1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 2
Server version: 5.5.33-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, 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>
|
|
1
2
3
4
5
6
|
[root@master ~]
# vim /etc/my.cnf
log-bin = mysql-bin
#二进制日志文件
log_bin_index = mysql_bin.index
#二进制日志文件索引
binlog_format = mixed
#设置日志格式为混合模式
server-
id
= 10
#用于识别的ID
[root@master ~]
# service mysqld restart #重启服务使配置文件生效
|
|
1
2
3
4
5
6
7
8
9
|
[root@slave ~]
# vim /etc/my.cnf
#binlog_format=mixed #注释此行
skip_slave_start = 1
#启动服务时不自动启动从服务线程
read_only = 1
#设置Slave服务器为只读
relay_log = relay_log
#开启中继日志文件
relay_log_index = relay_log.index
#开启中继日志文件索引
server-
id
= 20
#用户识别的ID号
#log-bin=mysql-bin #注释掉二进制日志文件,因为Master服务器已经记录了一份,这里没有必要再记录一份,避免浪费资源
[root@slave ~]
# service mysqld restart #重启服务使配置生效
|
|
1
2
3
4
5
6
7
8
9
|
[root@master ~]
# mysql -e 'show master status;'
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 107 | | |
+------------------+----------+--------------+------------------+
注释：
File：表示从此日志开始复制
Position：表示从这个事件开始复制
|
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> change master to master_host=
'172.16.14.1'
,master_user=
'allen'
,master_password=
'p@ssword'
,
master_port=3306,master_log_file=
'mysql-bin.000004'
,master_log_pos=107;
============================================================================
######猎取指令帮助
mysql> help change master to
CHANGE MASTER TO
MASTER_HOST=
'master.allen.com'
,
#主机名称
MASTER_USER=
'allen'
,
#连接Master服务器的授权用户
MASTER_PASSWORD=
'p@ssword'
,
#授权用户密码
MASTER_PORT=3306,
#端口
MASTER_LOG_FILE=
'mysql-bin.000004'
,
#二进制日志文件
MASTER_LOG_POS=107,
#二进制日志事件位置
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
|
mysql> start slave;
#启动Slave服务器线程
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting
for
master to send event
Master_Host: 172.16.14.1
#Master服务器地址
Master_User: allen
#连接Master服务器用户名
Master_Port: 3306
#Master服务器监听端口
Connect_Retry: 60
#重试时间间隔
Master_Log_File: mysql-bin.000004
#I/O线程读取的二进制日志文件
Read_Master_Log_Pos: 107
#I/O线程读取的二进制日志文件事件位置
Relay_Log_File: relay_log.000002
#SQL线程正在读取的中继日志文件
Relay_Log_Pos: 253
#SQL线程读取和执行的中继日志文件事件位置
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
#Slave服务器IO线程状态
Slave_SQL_Running: Yes
#Slave服务器SQL线程状态
Replicate_Do_DB:
#下面Replicate开头的表示用来指明哪些库或者表在复制时不需要同步
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
#SQL线程读取日志参数的错误数量
Last_Error:
#SQL线程读取日志参数的错误消息
Skip_Counter: 0
#最近被用于SQL_SLAVE_SKIP_COUNTER的值
Exec_Master_Log_Pos: 107
Relay_Log_Space: 403
#所有原有中继日志的总大小
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
#是否允许对Master服务器进行SSL连接
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
#落后于Master服务器的时间
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: 10
|
|
1
2
3
4
5
6
7
8
|
[root@slave ~]
# mysql -e 'show processlist;'
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 851 | Waiting
for
master to send event | NULL |
| 2 | system user | | NULL | Connect | 851 | Slave has
read
all relay log; waiting
for
the slave I
/O
thread to update it | NULL |
| 20 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
######在Master服务器创建数据库并查看
[root@master ~]
# mysql -e 'create database allen;'
[root@master ~]
# mysql -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| allen |
| mysql |
| performance_schema |
|
test
|
+--------------------+
===========================================================
######在Slave服务器查看是否有"allen"数据库
[root@slave ~]
# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| allen |
#数据库已成功同步到Slave服务器
| mysql |
| performance_schema |
|
test
|
+--------------------+
|
|
1
2
3
4
5
6
7
8
9
10
11
|
######查看Master服务器
[root@master ~]
# mysql -e 'show master status;'
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 192 | | |
+------------------+----------+--------------+------------------+
==========================================================================
######查看Slave服务器
[root@slave ~]
# mysql -e 'show slave status\G;' | grep "Read_Master_Log_Pos"
Read_Master_Log_Pos: 192
|
|
1
2
3
4
|
######查看半同步插件
ls
/usr/local/mysql/lib/plugin
semisync_master.so
#用于Master服务器安装的半同步插件
semisync_slave.so
#用于Slave服务器安装的半同步插件
|
|
1
2
3
4
5
6
7
8
9
10
11
|
######在Master服务器安装半同步插件
[root@master ~]
# mysql
mysql>
install
plugin rpl_semi_sync_master soname
'semisync_master.so'
;
#安装Master半同步插件
mysql>
set
global rpl_semi_sync_master_enabled = 1;
#开启Master半同步功能
mysql>
set
global rpl_semi_sync_master_timeout = 1000;
=========================================================================
######在Slave服务器安装半同步插件
[root@slave ~]
# mysql
mysql>
install
plugin rpl_semi_sync_slave soname
'semisync_slave.so'
;
#安装Slave半同步插件
mysql>
set
global rpl_semi_sync_slave_enabled = 1;
#开启Slave半同步功能
mysql> stop slave io_thread;start slave io_thread;
#重启IO线程生效
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
######在Master服务器上查看
mysql> show global status like
'rpl_semi%'
;
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
#已经有一个客户端连接
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
#已经为开启状态
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
mysql> show global variables like
'%rpl%'
;
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_recovery_rank | 0 |
| rpl_semi_sync_master_enabled | ON |
#Master半同步已经开启
| rpl_semi_sync_master_timeout | 1000 |
#超时时间
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
=========================================================================
######在Slave服务器上查看
mysql> show global status like
'rpl_semi%'
;
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
#已经为开启状态
+----------------------------+-------+
mysql> show global variables like
'%rpl%'
;
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_recovery_rank | 0 |
| rpl_semi_sync_slave_enabled | ON |
#Slave半同步已经开启
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
|
|
1
2
3
4
|
[root@slave ~]
# mysql -e 'show slave status\G;' | grep Running
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
注释：这两项必须为
"Yes"
,如果是
"No"
说明启动失败
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
######在Master服务器删除数据库
[root@master ~]
# mysql -e 'drop database allen;'
[root@master ~]
# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
|
test
|
+--------------------+
=======================================================
######在Slave服务器查看
[root@slave ~]
# mysql -e 'show databases;'
+--------------------+
| Database | 注释：已经成功删除
"allen"
数据库
+--------------------+
| information_schema |
| mysql |
| performance_schema |
|
test
|
+--------------------+
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
[root@master ~]
# cd /etc/pki/CA/
[root@master CA]
# (umask 077;openssl genrsa -out private/cakey.pem 2048)
[root@master CA]
# openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 365
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) [XX]:CN
State or Province Name (full name) []:ShangHai
Locality Name (eg, city) [Default City]:PuDong
Organization Name (eg, company) [Default Company Ltd]:Allen
Organizational Unit Name (eg, section) []:Tech
Common Name (eg, your name or your server's
hostname
) []:master.allen.com
Email Address []:
[root@master CA]
# touch index.txt
[root@master CA]
# echo 01 > serial
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
[root@master CA]
# mkdir /usr/local/mysql/ssl
[root@master CA]
# cd /usr/local/mysql/ssl
[root@master ssl]
# (umask 077;openssl genrsa -out master.key 2048)
[root@master ssl]
# openssl req -new -key master.key -out master.csr -days 365
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) [XX]:CN
State or Province Name (full name) []:ShangHai
Locality Name (eg, city) [Default City]:PuDong
Organization Name (eg, company) [Default Company Ltd]:Allen
Organizational Unit Name (eg, section) []:Tech
Common Name (eg, your name or your server's
hostname
) []:master.allen.com
Email Address []:master@allen.com
Please enter the following
'extra'
attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[root@master ssl]
# openssl ca -in master.csr -out master.crt -days 365
Using configuration from
/etc/pki/tls/openssl
.cnf
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 1 (0x1)
Validity
Not Before: Sep 20 12:22:19 2013 GMT
Not After : Sep 20 12:22:19 2014 GMT
Subject:
countryName = CN
stateOrProvinceName = ShangHai
organizationName = Allen
organizationalUnitName = Tech
commonName = master.allen.com
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
16:89:07:36:58:C9:AD:7B:97:D6:77:2E:13:FB:66:4F:A9:2B:3E:A3
X509v3 Authority Key Identifier: keyid:D8:0B:06:3B:6B:1B:36:88:17:56:EB:2A:41:1A:20:A4:89:7F:97:6A
Certificate is to be certified
until
Sep 20 12:22:19 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
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
[root@slave ~]
# mkdir /usr/local/mysql/ssl
[root@slave ~]
# cd /usr/local/mysql/ssl
[root@slave ssl]
# (umask 077;openssl genrsa -out slave.key 2048)
[root@slave ssl]
# openssl req -new -key slave.key -out slave.csr -days 365
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) [XX]:CN
State or Province Name (full name) []:ShangHai
Locality Name (eg, city) [Default City]:PuDong
Organization Name (eg, company) [Default Company Ltd]:Allen
Organizational Unit Name (eg, section) []:Tech
Common Name (eg, your name or your server's
hostname
) []:slave.allen.com
Email Address []:
Please enter the following
'extra'
attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
######将证书申请请求拷贝到CA服务器签署
[root@slave ssl]
# scp slave.csr master.allen.com:/tmp/
[root@master ~]
# openssl ca -in /tmp/slave.csr -out /tmp/slave.crt -days 365
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: Sep 20 12:32:55 2013 GMT
Not After : Sep 20 12:32:55 2014 GMT
Subject:
countryName = CN
stateOrProvinceName = ShangHai
organizationName = Allen
organizationalUnitName = Tech
commonName = slave.allen.com
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier: 4E:19:98:5D:F5:D2:D1:71:8B:93:4F:84:3C:A2:C7:2C:FE:6D:E2:62
X509v3 Authority Key Identifier: keyid:D8:0B:06:3B:6B:1B:36:88:17:56:EB:2A:41:1A:20:A4:89:7F:97:6A
Certificate is to be certified
until
Sep 20 12:32:55 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
######签署好证书申请拷贝到Slave服务器
[root@master ~]
# scp /tmp/slave.crt slave.allen.com:/usr/local/mysql/ssl/
|
|
1
2
|
[root@master ~]
# scp /etc/pki/CA/cacert.pem slave.allen.com:/usr/local/mysql/ssl/
[root@master ~]
# cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl/
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
######修改Master服务器
[root@master ~]
# chown -R mysql.mysql /usr/local/mysql/ssl
[root@master ~]
# ll /usr/local/mysql/ssl/
-rw-r--r-- 1 mysql mysql 1415 Sep 20 20:57 cacert.pem
-rw-r--r-- 1 mysql mysql 4600 Sep 20 20:22 master.crt
-rw-r--r-- 1 mysql mysql 1054 Sep 20 20:20 master.csr
-rw------- 1 mysql mysql 1675 Sep 20 20:17 master.key
===============================================================
######修改Slave服务器
[root@slave ~]
# chown -R mysql.mysql /usr/local/mysql/ssl
[root@slave ~]
# ll /usr/local/mysql/ssl/
-rw-r--r-- 1 mysql mysql 1415 Sep 15 03:10 cacert.pem
-rw-r--r-- 1 mysql mysql 4598 Sep 15 03:05 slave.crt
-rw-r--r-- 1 mysql mysql 1054 Sep 15 03:00 slave.csr
-rw------- 1 mysql mysql 1675 Sep 15 02:59 slave.key
注意：Master与Slave服务器上的证书属主、属组必须为mysql用户及组
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
######修改Master服务器
[root@master ~]
# vim /etc/my.cnf #添加如下选项
ssl
#开启SSL功能
ssl_ca =
/usr/local/mysql/ssl/cacert
.pem
#指定CA文件位置
ssl_cert =
/usr/local/mysql/ssl/master
.crt
#指定证书文件位置
ssl_key =
/usr/local/mysql/ssl/master
.key
#指定密钥所在位置
[root@master ~]
# service mysqld restart #重启服务生效
====================================================================
######修改Slave服务器
[root@slave ~]
# vim /etc/my.cnf
ssl
ssl_ca =
/usr/local/mysql/ssl/cacert
.pem
ssl_cert =
/usr/local/mysql/ssl/slave
.crt
ssl_key =
/usr/local/mysql/ssl/slave
.key
[root@slave ~]
# service mysqld restart
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
[root@master ~]
# mysql
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/master
.crt |
| ssl_cipher | |
| ssl_key |
/usr/local/mysql/ssl/master
.key |
+---------------+---------------------------------+
mysql> grant replication client,replication slave on *.* to
'slave'
@
'172.16.%.%'
identified by
'passwd'
require ssl;
mysql> flush privileges;
|
|
1
2
3
4
5
6
|
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 350 | | |
+------------------+----------+--------------+------------------+
|
|
1
2
3
4
5
6
7
8
9
10
|
[root@slave ~]
# mysql -uslave -ppasswd -h 172.16.14.1 --ssl-ca=/usr/local/mysql/ssl/cacert.pem --ssl-cert=/usr/local/mysql/ssl/slave.crt --ssl-key=/usr/local/mysql/ssl/slave.key
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 5
Server version: 5.5.33-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, 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>
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
######查看Slave服务器SSL是否开启
[root@slave ~]
# mysql
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/slave
.crt |
| ssl_cipher | |
| ssl_key |
/usr/local/mysql/ssl/slave
.key |
+---------------+---------------------------------+
######连接Master服务器
mysql> change master to master_host=
'172.16.14.1'
,master_user=
'slave'
,master_password=
'passwd'
,
master_log_file=
'mysql-bin.000004'
,master_log_pos=350,master_ssl=1,
master_ssl_ca=
'/usr/local/mysql/ssl/cacert.pem'
,
master_ssl_cert=
'/usr/local/mysql/ssl/slave.crt'
,
master_ssl_key=
'/usr/local/mysql/ssl/slave.key'
;
|
|
1
2
3
4
5
6
|
######获取命令帮助
mysql> help change master to
| MASTER_SSL = {0|1}
#是否使用SSL功能
| MASTER_SSL_CA =
'ca_file_name'
#CA证书位置
| MASTER_SSL_CERT =
'cert_file_name'
#指定自己的证书文件
| MASTER_SSL_KEY =
'key_file_name'
#指定自己的密钥文件
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.14.1
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 350
Relay_Log_File: relay_log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-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: 350
Relay_Log_Space: 107
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:
Master_SSL_Cert:
/usr/local/mysql/ssl/slave
.crt
Master_SSL_Cipher:
Master_SSL_Key:
/usr/local/mysql/ssl/slave
.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
|
|
1
2
3
4
5
6
7
8
9
10
11
|
[root@master ~]
# mysql -e 'create database slave;'
[root@master ~]
# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| slave |
|
test
|
+--------------------+
|
|
1
2
3
4
5
6
7
8
9
10
|
[root@slave ~]
# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| slave |
|
test
|
+--------------------+
|
136
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
