MySQL数据库之主从同步配置

作用

1）多台服务器，避免单点故障问题

2）数据复制，实现一致性

原理

主机增删改后记录到bin_log中，

从机启动IO线程读取bin_log到本地，启动SQL线程执行增删改同步本地数据库

步骤

前期工作

1）安装MySQL 8.0.27

安装地址：

https://dev.mysql.com/downloads/mysql/
https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz

1、下载上传并解压

# 上传至Linux
# 解压

[root@centos7 ~]# xz -d mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz
[root@centos7 ~]# tar xf mysql-8.0.27-linux-glibc2.12-x86_64.tar
[root@centos7 ~]# mv mysql-8.0.27-linux-glibc2.12-x86_64 mysql-8.0
[root@centos7 ~]# mv mysql-8.0 /usr/local/

2.卸载mariadb

# 查看mariadb 的安装包
[root@centos7 ~]# rpm -qa | grep mariadb

# 卸载 mariadb
[root@centos7 ~]# rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps
[root@centos7 ~]# rpm -qa | grep mariadb

# 安装依赖包
[root@centos7 ~]# yum install lrzsz vim libaio -y

3、创建用户及用户组

# 创建用户组
[root@centos7 ~]# groupadd mysql

# 创建用户
[root@centos7 ~]# useradd -g mysql mysql

# 授权权限
[root@centos7 ~]# chown -R mysql.mysql /usr/local/mysql-8.0/

4、初始化数据库

[root@centos7 ~]# cd /usr/local/mysql-8.0/
[root@centos7 ~]# ./bin/mysqld --user=mysql --basedir=/usr/local/mysql-8.0/ --datadir=/usr/local/mysql-8.0/data/ --initialize

5、配置MySQL

[root@centos7 ~]# vim /etc/my.cnf 
[mysqld]
basedir=/usr/local/mysql-8.0
datadir=/usr/local/mysql-8.0/data
socket=/tmp/mysql.sock
character-set-server=utf8

6、配置服务启动

[root@centos7 ~]# cd /usr/local/mysql-8.0/
[root@centos7 mysql-8.0]# cp -a ./support-files/mysql.server /etc/init.d/mysql
[root@centos7 ~]#chmod +x /etc/init.d/mysql 
[root@centos7 ~]#chkconfig --add mysql 
[root@centos7 ~]#chkconfig --list mysql

7、启动Mysql服务

[root@centos7 ~]# service mysql start
[root@centos7 ~]# service mysql status
[root@centos7 ~]# ln -s /usr/local/mysql-8.0/bin/mysql /usr/bin/

8、登陆Mysql

[root@centos7 ~]# mysql -uroot -p')TV<NZrdk9iL'
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '1';

9、远程连接

[root@centos7 ~]# vim /etc/my.cnf 
#添加下面一行
default_authentication_plugin=mysql_native_password

# 重启数据库
[root@centos7 ~]# service mysql restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

[root@centos7 ~]# mysql -uroot -p1
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set host ='%' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> alter user 'root'@'%' identified with mysql_native_password by '1';
Query OK, 0 rows affected (0.04 sec)

# 查看加密方式
mysql> select user,plugin from user where user='root'; 
+------+-----------------------+
| user | plugin                |
+------+-----------------------+
| root | mysql_native_password |
+------+-----------------------+
1 row in set (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

2）克隆两台MySQL服务器

根据需求设置虚拟机

3）修改IP，关闭防火墙

修改IP
vim /etc/sysconfig/network-scripts/ifcfg-ens33
防火墙
systemctl stop firewalld
systemctl disable firewalld

主机

1.配置my.cnf
[root@centos7 ~]# vi /etc/my.cnf
log-bin=mysql-bin-master
server-id=1
binlog-do-db=demo
binlog-ignore-db=mysql

注：克隆的机器uuid需要更改
修改 auto.cnf文件的UUID
[root@centos7 ~]# vi /usr/local/mysql-8.0/data/auto.cnf

重启数据库
[root@centos7 ~]# systemctl restart mysql

2.授权连接用户
创建用户
create user 'myroot'@'%' identified with mysql_native_password by '1';
授权
grant replication slave on *.* to 'myroot'@'%';
加载
flush privileges;

3.查看主服务器状态
show master status;

4.创建同步数据库	从机做完后再做，可测试同步是否成功
create database demo;;

从机

授权连接用户
创建用户
create user 'myroot'@'%' identified with mysql_native_password by '1';
授权
grant replication slave on *.* to 'myroot'@'%';
加载
flush privileges;

1.配置从1 my.cnf文件
[root@centos7 ~]# vi /etc/my.cnf 
log-bin=mysql-bin-master
server-id=2
binlog-do-db=demo
binlog-ignore-db=mysql
[root@centos7 ~]# systemctl restart mysql

2.进入mysql
[root@centos7 ~]# mysql -uroot -p1
从：
stop slave;
主：
根据主机中查询的修改
show master status;  ****** 主机 ******
+-------------------------+----------+--------------+------------------+-------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-master.000003 |      155 | demo         | mysql            |                   |
+-------------------------+----------+--------------+------------------+-------------------+

从1：
change master to master_host='10.0.0.41',master_user='myroot',master_password='1',master_port=3306,master_log_file='mysql-bin-master.000001',master_log_pos=809;

start slave;

show slave status\G
检查：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

6）常见问题

IO no
连不上主机 cant connect to host xxx
原因：1）防火墙 2）主机ip 3）账号密码 4）账号授  权
SQL no
看下面提示，解决
1）删除相同名称数据库
2）修改pos位置，再启动主从
...
6）测试

在主机的同步数据库中建表或插入数据，进入从机查看

测试

主机插入内容，从机查看测试：
主机：
mysql> use demo;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table test(
    -> id int,
    -> name varchar(20));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test values (1,'张三');
Query OK, 1 row affected (0.08 sec)

从：
mysql> use demo;

mysql> select * from test;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
+------+--------+