原创作品，允许转载，转载时请务必以超链接形式标明文章
原始出处 、作者信息和本声明。否则将追究法律责任。
http://8941355.blog.51cto.com/8931355/1712394
Centos7默认使用的是MariaDB，要想安装Mysql，需要先卸载掉MariaDB，防止冲突。
1：安装mysql数据库
规划：
主：server-1---10.64.5.167
从：server-2---10.64.5.170
从：server-3---10.64.5.172
数据目录：/var/lib/mysql
添加yum源：
|
1
2
|
#wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
#rpm -ivh mysql-community-release-el7-5.noarch.rpm
|
安装包：
|
1
|
#yum install mysql
|
mysql-community-common-5.6.27-2.el7.x86_64
mysql-community-libs-5.6.27-2.el7.x86_64
mysql-community-server-5.6.27-2.el7.x86_64
mysql-community-client-5.6.27-2.el7.x86_64
自启动：
|
1
|
#systemctl enable mysqld
|
2：配置mysql主库
主：server-1 10.64.5.167
（1）配置创建需要同步的数据库cattle。
|
1
2
3
4
|
#mysql
mysql>CREATEDATABASE IF NOT EXISTS cattle COLLATE=
'utf8_general_ci'
CHARACTER SET=
'utf8'
;
mysql>GRANT ALL ON cattle.*TO
'cattle'
@
'%'
IDENTIFIED BY
'cattle'
;
mysql>GRANT ALL ON cattle.*TO
'cattle'
@
'localhost'
IDENTIFIED BY
'cattle'
;
|
（2）创建用户
|
1
2
3
|
mysql> GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO slave1@
'10.64.5.170'
IDENTIFIED BY
'123456'
;
mysql> GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO slave2@
'10.64.5.172'
IDENTIFIED BY
'123456'
;
mysql> flush privileges;
|
（3）配置文件
|
1
2
3
4
5
6
|
#vim /etc/my.cnf
添加
server-
id
= 1
log-bin=mysql-bin
log-slave-updates
binlog-
do
-db=cattle
binlog-ignore-db=mysql
|
重启mysql
（4）锁主库表
|
1
|
mysql> FLUSH TABLES WITH READ LOCK;
|
（4）显示主库信息
|
1
|
mysql> SHOW MASTER STATUS;
|
（5）另开一个终端，打包主库
|
1
2
|
#cd /var/lib/mysql
#tar czvf cattle.tar.gz cattle
|
（6）解锁主库表
|
1
|
mysql> UNLOCK TABLES;
|
3：配置mysql从库
从：server-2 10.64.5.170
从：server-3 10.64.5.172
（1）将cattle.tar.gz 传输到slav机器
|
1
2
3
|
#mv cattle.tar.gz /var/lib/mysql/
#cd /var/lib/mysql/
#tar xf cattle.tar.gz
|
（2）查看修改cattle文件夹权限
|
1
|
#chown -R mysql:mysql cattle
|
（3）配置文件
|
1
2
3
4
5
6
7
8
|
#vim /etc/my.cnf
------------server-2 添加
server-
id
=2
log_bin = mysql-bin
relay_log = mysql-relay-bin
read
-only=1
replicate-
do
-db=cattle
log-slave-updates=1
|
|
1
2
3
4
5
6
7
|
------------server-3 添加
server-
id
=3
log_bin = mysql-bin
relay_log = mysql-relay-bin
read
-only=1
replicate-
do
-db=cattle
log-slave-updates=1
|
重启slave的mysql
（4）验证连接
从库server-2上测试连接主库
|
1
2
|
#mysql -h10.64.5.167 -uslave1 -p123456
mysql> show grants
for
slave1@10.64.5.170；
|
从库server-3上测试连接主库
|
1
2
|
# mysql -h10.64.5.167 -uslave2 -p123456
mysql> show grants
for
slave2@10.64.5.172；
|
（5）设置slave复制
查询master的position值
|
1
2
3
4
5
6
7
8
9
|
mysql> SHOW MASTER STATUS\G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 120
Binlog_Do_DB: cattle
Binlog_Ignore_DB: mysql
1 row
in
set
(0.00 sec)
ERROR:
No query specified
|
|
1
2
3
4
5
6
|
server-2配置
mysql>CHANGE MASTER TO MASTER_HOST=
'10.64.5.167'
,
-> MASTER_USER=
'slave1'
,
-> MASTER_PASSWORD=
'123456'
,
-> MASTER_LOG_FILE=
'mysql-bin.000001'
,
-> MASTER_LOG_POS=120; -------MASTER_LOG_POS为主库的Position
|
|
1
2
3
4
5
6
|
server-3配置
mysql>CHANGE MASTER TO MASTER_HOST=
'10.64.5.167'
,
-> MASTER_USER=
'slave2'
,
-> MASTER_PASSWORD=
'123456'
,
-> MASTER_LOG_FILE=
'mysql-bin.000001'
,
-> MASTER_LOG_POS=120;
|
（6）slave启动
|
1
2
|
mysql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)
|
运行SHOW SLAVE STATUS查看输出结果：
主要查看
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
|
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> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting
for
master to send event
Master_Host: 10.64.5.167
Master_User: slave1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 120
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 178995708
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: cattle
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: 178995562
Relay_Log_Space: 178995864
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
1 row
in
set
(0.00 sec)
|
验证master
|
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
|
mysql> SHOW PROCESSLIST\G;
*************************** 1. row ***************************
Id: 14
User: system user
Host:
db: NULL
Command: Connect
Time: 63424
State: Connecting to master
Info: NULL
*************************** 2. row ***************************
Id: 15
User: system user
Host:
db: NULL
Command: Connect
Time: 63424
State: Slave has
read
all relay log; waiting
for
the slave I
/O
thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 25
User: slave1
Host: 10.64.5.170:47139
db: NULL
Command: Binlog Dump
Time: 62967
State: Master has sent all binlog to slave; waiting
for
binlog to be updated
Info: NULL
*************************** 4. row ***************************
Id: 244
User: slave2
Host: 10.64.5.172:45629
db: NULL
Command: Binlog Dump
Time: 53898
State: Master has sent all binlog to slave; waiting
for
binlog to be updated
Info: NULL
16 rows
in
set
(0.00 sec)
ERROR:
No query specified
|
4：验证主从同步
在主库server-1中创建一个表
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> USE cattle;
Database changed
mysql> CREATE TABLE `
test
` (`name` varchar(10) NULL ,`old` char(10) NULL );
Query OK, 0 rows affected (0.00 sec)
mysql> DESC
test
;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
| old | char(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows
in
set
(0.00 sec)
|
从库查询是否有这个新表
|
1
2
3
4
5
6
7
8
9
10
|
mysql> USE cattle;
Database changed
mysql> DESC
test
;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
| old | char(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows
in
set
(0.01 sec)
|
至此，mysql的主从复制完成。
备注: 使用vmware镜像 ,两个mysql的 auto.cnf一样,会报错
进入/var/lib/mysql/ 删除 auto.cnf
报错查看 日志:
cat /var/log/mysql.log
