mysql 主备模式搭建过程

1
2
3
centos7：# tar -xvf mysql-5.7.21-1.el7.x86_64.rpm-bundle.tar //注意这里是tar格式
centos6：# tar -xvf mysql-5.7.23-1.el6.x86_64.rpm-bundle.tar //注意这里是tar格式

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql-community-client-5.7.21-1.el7.x86_64.rpm
mysql-community-common-5.7.21-1.el7.x86_64.rpm
mysql-community-server-minimal-5.7.21-1.el7.x86_64.rpm
mysql-community-devel-5.7.21-1.el7.x86_64.rpm
mysql-community-embedded-5.7.21-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.21-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.21-1.el7.x86_64.rpm
mysql-community-libs-5.7.21-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.21-1.el7.x86_64.rpm
mysql-community-minimal-debuginfo-5.7.21-1.el7.x86_64.rpm
mysql-community-server-5.7.21-1.el7.x86_64.rpm
mysql-community-test-5.7.21-1.el7.x86_64.rpm

1
2
3
centos7：# rm -rf mysql-community-server-minimal-5.7.21-1.el7.x86_64.rpm
centos6：# rm -rf mysql-community-server-minimal-5.7.23-1.el6.x86_64.rpm

1
2
yum install mysql-community-{server,client,common,libs}-*

1
2
3
systemctl start mysqld.service
systemctl enable mysqld.service

1
2
3
4
grep 'temporary password' /var/log/mysqld.log
mysql -u root -p
输入查询到的临时密码,以登录MySQL

1
2
3
4
ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';
update mysql.user set Host='%' where HOST='localhost' and User='root';
flush privileges;

1
2
3
mysql>GRANT REPLICATION SLAVE ON *.* to '用户名'@'从数据库IP地址' identified by '密码';
mysql>FLUSH PRIVILEGES;

1
2
3
4
5
6
7
vim /etc/my.cnf
log-bin=/var/lib/mysql/binlog #同步的日志路径及文件名，一定注意这个目录要是mysql有权限写入的
server-id=1 #master端的id号，不唯一即可
#binlog-do-db = cmdb #指定同步的数据库，如果不写，默认是同步所有数据库
expire_logs_days=7 #binlog日志的保留时间
binlog_format=ROW #指定binlog日志的格式

1
2
service mysqld restart

1
2
3
4
5
6
7
8
9
10
11
12
mysql> show master status;
+---------------+----------+--------------+------------------+
| File
| Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000001 |
1304 | cmdb
|
|
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

1
2
3
4
vim /etc/my.cnf
server-id=2 #slave端的id号，不唯一即可
read_only=1 #从库的普通用户的只读权限

1
2
service mysqld restart

1
2
3
4
5
6
7
8
mysql> CHANGE MASTER TO MASTER_HOST='主库的ip',
MASTER_PORT=3306,
MASTER_USER='用户名',
MASTER_PASSWORD='密码',
MASTER_LOG_FILE='binlog.000002',
MASTER_LOG_POS=1943;
注：MASTER_LOG_FILE和MASTER_LOG_POS的值为master节点中执行show master status查询到的信息

1
2
3
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)

1
2
3
4
5
mysql> show slave statusG;
如果下面两项值为YES，则表示配置正确：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes