MySQL主从复制验证性配置操作

前置条件：
VMware Workstation Pro、CentOS7
防火墙关闭
两台同网段虚机dw2（ip：192.168.203.202），dw3（ip：192.168.203.203）

1. 数据库安装和初始化

yum install -y mariadb mariadb-server mariadb-devel

vim /etc/my.cnf

# 打开mysql-bin功能
[mysqld]
server-id=2
log-bin=mysql-bin

# 启动maridb，初始化
systemctl start mariadb

mysql_secure_installation

2. 更改默认字符集

# 查看MySQL字符集
mysql -uroot -proot

show variables where variable_name like 'characert_set_%' or variable_name like 'collation%';

quit;

# 修改配置文件
vim /etc/my.cnf.d/server.cnf

[mysqld]
character-set-client-handshake=FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

# 重新启动mariadb
systemctl restart mariadb

3. 设置远程连接

select host,user from user where user='root';

update user set host='%' where host='localhost';

flush privileges

select host,user from user where user='root';

4. 主从配置

锁定主数据库

flush tables with read lock;

创建主数据库的连接用户

# admin用户名可任意更改
grant replication slave on *.* to 'admin'@'192.168.203.203' identified by 'root';

解锁

unlock tables;

grant replication slave on *.* to 'admin'@'192.168.203.203' identified by 'root';

show master status;

从节点设置master

mysql -uroot -proot

change master to master_host='192.168.203.202',master_user='root',master_password='root',master_log_file='mysql-bin.000004',master_log_pos=392;

slave start;

show slave status \G;