CentOS7下MySQL主从复制搭建

发表于 | 分类于

版本信息

  • CentOS Linux release 7.6.1810 (Core)

下载MySQL源

由于MySQL已被Oracle收购，存在闭源的风险，所以在CentOS7中已将MySQL从CentOS的默认软件中删除，换成了MariaDB。
MariaDB是MySQL的一个分支，完全兼容MySQL。同时又更新了一些新功能。其性能比MySQL要更好。

这里master中安装MySQL，slave中安装MariaDB
后来发现slave安装MariaDB后无法同步主库数据，遂改为主从均为MySQL，改后发现正常同步,所以应尽量保持版本的一致性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# master and slave
# 下载yum源
$ wget https://repo.mysql.com//mysql80-community-release-el7-2.noarch.rpm
# 安装源
$ rpm -ivh mysql80-community-release-el7-2.noarch.rpm
# 编辑源
$ vim /etc/yum.repos.d/mysql-community.repo
# edit /etc/yum.repos.d/mysql-community.repo
# 这里安装MySQL5.7
# 将 MySQL 5.7 Community Server enabled=0 改为 enabled=1
# 将 MySQL 8.0 Community Server enabled=1 改为 enabled=0

# 查看yum源是否安装成功
$ yum repolist enabled | grep "mysql.*-community.*"
# 安装mysql
$ yum -y install mysql-community-server

配置

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
# master and slave
# 设置编码 /etc/my.cnf
[client]
default-character-set=utf8

[mysqld]
character_set_server=utf8
collation-server = utf8_general_ci
------------------------------------------------
# 查看编码
mysql>  show variables like 'character%';
------------------------------------------------
# 主从复制 /etc/my.cnf
[mysqld]
log_bin = binlog # 启用二进制日志
server_id = 22   # 服务器ID，默认1，一般取ip地址后一字节
------------------------------------------------
$ systemctl restart mysqld

# master 添加同步共享用户
mysql> create user 'admin'@'localhost' identified by 'admin';
mysql> create user 'admin'@'10.211.55.24' identified by 'admin';
mysql> create database admin_test;
mysql> grant all privileges on admin_test.* to admin@localhost identified by 'admin';
mysql> grant all privileges on admin_test.* to admin@127.0.0.1 identified by 'admin';
mysql> grant all privileges on admin_test.* to admin@10.211.55.24 identified by 'admin';
mysql> flush privileges;
格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";

mysql> grant replication slave on *.* to 'admin'@'10.211.55.24' identified by 'admin';
mysql> flush privileges;
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |     2569 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# slave 添加同步共享用户
mysql> create user 'admin'@'localhost' identified by 'admin';
mysql> create user 'admin'@'10.211.55.22' identified by 'admin';
mysql> change master to master_host='10.211.55.22',master_user='admin',master_password='admin',master_log_file='binlog.000001',master_log_pos=2569;
mysql> start slave;
mysql> show slave status\G;
# out:
# ......
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
# ......

参考文章：

Q&A

  • MySQL 安装完毕之后，会生成一个默认的临时密码在 /var/log/msyqld.log 文件中，使用该临时密码登录后需要修改密码
  • 报错信息为： 
1
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
  • 修改密码：
1
SET PASSWORD= PASSWORD('PASSWORD')
  • 又出现报错：
1
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
  • 这是由于和mysql的密码安全性的策略有关，”validate_password_policy”
Policy Tests Performaed
0 or LOW 符合一定长度(默认长度一把为8)，最小长度为4，
1 or MEDIUM 符合一定长度，含有数字，小写或大写字母，特殊字符
2 or STRONG 符合一定长度，数字，小写或大写字母,特殊字符,字典文件

解决方法： 

1
2
3
mysql> set global validate_password_policy=0; 
mysql> set global validate_password_length=4;
mysql> set password = password('123456');

参考文章：