MySQL主从同步

1.主从同步机制

MySQL数据库的主从同步机制, 是通过复制主服务器的binlog日志到需要同步的从服务器

1. master将改变记录到二进制日志(binary log)中(这些记录叫做二进制日志事件,binary log events）
2. slave将master的binary log events拷贝到它的中继日志(relay log)
3. slave重做中继日志中的事件,将改变反映它自己的数据

使用主从同步的优点 :

• 提高读写性能
• 提高数据安全

2.Docker安装Mariadb从机

1.指定从机配置文件Mariadb配置文件

#拷贝主机配置文件到从机
cd /opt
mkdir slave_mariadb
cd slave_mariadb
mkdir  data
cp -r /etc/mysql.conf.d ./

2.修改从机配置文件

vi /opt/slave_mariadb/my.cnf

[mysqld]
# 从机唯一编号
server-id=2
# 从机端口号
port = 8306

3.拉取镜像

docker image pull mariadb:10.1.48

4.安装镜像

# -v 参数的作用是将宿主机的一个目录(绝对路径)作为容器的数据卷挂载到docker容器中，使宿主机和容器之间可以共享一个目录，如果本地路径不存在，Docker也会自动创建
# /var/lib/mysql/ 数据库数据存放位置
# -d 守护进程
# 指定软件及软件的版本号 mariadb:10.1.48
# --network=host 共享主机的网络命名空间
# -p 3306:3306
docker run --name slave_mariadb -e MYSQL_ROOT_PASSWORD=mysql -d -p 8306:3306  -v /opt/slave_mariadb/data/:/var/lib/mysql -v /opt/slave_mariadb/my.cnf.d:/etc/my.cnf.d  mariadb:10.1.48 

5.常见的容器操作

# 查看容器日志
docker logs 容器id
# 查看容器所在位置
docker inspect --format '{{.LogPath}}' 容器id
# 进入容器内部
docker exec -it 容器id /bin/bash
# 查看运行中的容器
docker ps
#查看所有容器(包括暂停的)
docker ps -e
# 暂停当前容器
docker stop 容器id
#移除当前容器
docker rm 容器id

6.测试从机是否创建成功

mysql -uroot -pmysql -h 127.0.0.1 --port=8306

7.检查Slava从数据库的各项参数

show variables like 'log_bin';
show variables like 'server_id';

3.配置master主机

1.暂停数据库

systemctl stop mariadb

2.修改配置文件 vim /etc/my.cnf

#解释：server-id服务的唯一标识（主从之间都必须不同）；log-bin启动二进制日志名称为mysql-bin 
[mysqld]
server-id=1
#log-bin启动二进制日志名称
log-bin=mysql-bin

3.重启mariadb

systemctl start mariadb

4.从机备份主机原有数据

#收集主机原有数据
mysqldump -uroot -pmysql --all-databases --lock-all-tables > ~/master_db.sql
# 从机复制主机原有数据
$ mysql -uroot -pmysql -h127.0.0.1 --port=8306 < ~/master_db.sql

5.创建用于从服务器同步数据的帐号

#方法一
#创建用户,新建用于主从同步的用户slave,允许登录的从库是'%'
create user 'slave'@'%' identified by 'mysql';
#赋予权限,给从库账号授权,给slave从库复制的权限,在"%"机器上复制
grant replication slave on *.* to 'username'@'%';
# 刷新权限
FLUSH PRIVILEGES;

#方法二
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by 'slave'
grant replication slave on *.*  to 'slave'@'%' identified by 'slave'
# 刷新权限
FLUSH PRIVILEGES;

6.检查

#检查主库创建的复制账号
select user,host from mysql.user;
#检查授权账号的权限
show grants for 'slave'@'%';
#检查主库的状态,其中Position 是日志开始的位置
show master status

4.配置从机

# 登录到从机
mysql -uroot -pmysql -h 127.0.0.1 --port=8306
# 从机连接到主机
change master to master_host='127.0.0.1', master_user='slave', master_password='slave',master_log_file='mysql-bin.000001', master_log_pos=575;
# 开启从机服务
start slave;
# 展示从机服务状态
show slave status \G

注意

此处还未配置从库的只读模式,只需在slave服务器上配置/etc/my.cnf,加上以下配置,并且在slave上创建普通用户,使用普通用户主从同步即可达到只读的效果,如果用root用户,无法达到readonly，这是一个坑

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
log-error=/var/log/mysqld.log
server-id=3
read-only=true
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

5.补充

问题1:ERROR 1045 (28000): Access denied for user 'root'@'172.17.0.1' (using password: YES)

#使用localhost登录
mysql -uroot -pmysql -h localhost
#赋予远程访问权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'mysql' WITH GRANT OPTION;
flush privilege
#修改密码
update mysql.user set Password=password('Admin_123') where User="root";
#查看数据库端口全局变量
show global variables like 'port';