Mysql数据库主从配置:
提前准备
三台可以互相可以通连的服务器(或虚拟机)
已经安装好Mysql并且可以正常使用
我使用的3台服务器的IP分别为192.160.56.100,192.160.56.110,192.160.56.120
开始配置主数据库(Master)
- 192.160.56.100服务器为主,在服务器上找到mysql的配置文件进行修改
vi /etc/my.cnf
- 增加如下内容
# 指定一个server的id,随便起只要不重复即可,因为这台是主,所以直接给1
server-id=1
# 开启binlog日志并且指定日志的位置,从服务器就是根据这个日志做数据同步的
log_bin=/var/log/mysql-bin.log
# 日志的缓存时间,设置5天
expire_logs_days=5
#日志的最大大小,设置5G
max_binlog_size=5G
#同步的数据库名称
binlog_do_db=toyota
#忽略同步的数据库
#binlog_ignore_db = mysql
- 配置好以后,发现服务无法启动,查看mysql日志,会看到是一个权限的问题,这是由于配置了binlog日志所在路径没有权限
- 给binlog所在路径赋予权限,然后再启动mysql可以正常启动
chmod 777 /var/log/
开配置从数据库(slave)
- 192.160.56.110-120服务器为slave,在服务器上找到mysql的配置文件进行修改
vi /etc/my.cnf
- 添加如下内容
#设置一个serviceid,不允许与其他服务器的重复
server-id=2
#日志的缓存时间,5天
expire_logs_days=5
#日志的最大大小,5G
max_binlog_size=5G
#同步的数据库名称
replicate_do_db=toyota
#忽略同步的数据库
#replicate_ignore_db = mysql
- 重起mysql服务
systemctl restart mysqld.service
创建同步用户并且开始复制
- 登录进mysql,然后执行如下命令
CREATE USER 'syncuser'@'%' IDENTIFIED BY 'Cmbc,1234';
- 赋予权限
GRANT REPLICATION SLAVE ON *.* TO 'syncuser'@'%' IDENTIFIED BY 'Cmbc,1234';
- 刷新用户权限
flush privileges;
- 查看主的状态
show master status;
- 在两个从数据上分别执行下面语句
change master to master_host='192.168.56.100', master_user='syncuser', master_password='Cmbc,1234', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=842;
- 说明
- 启动复制线程
start slave;
- 查看是否成功,在从服务器执行以下sql
show slave status\G
- 如果下面红框中的两个都为Yes表示配置成功
- 停止从服务器复制动作
stop slave;
从服务器配置多线程的复制方式
- 查看当前的复制方式
show variables like "slave_parallel_type";
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| slave_parallel_type | DATABASE |
+---------------------+----------+
1 row in set (0.01 sec)
- 查看止前的复制线程数
show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 2 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 3 | system user | | NULL | Connect | 919 | Waiting for master to send event | NULL |
| 4 | system user | | NULL | Connect | 919 | Slave has read all relay log; waiting for more updates | NULL |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
- 停止复制
stop slave;
- 配置以并发线程的方式进行复制
set global slave_parallel_type = "logical_clock";
Query OK, 0 rows affected (0.00 sec)
- 配置并发线程数
set global slave_parallel_workers = 4;
Query OK, 0 rows affected (0.00 sec)
- 再次查看多线程的复制类型
show variables like "slave_parallel_workers";
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| slave_parallel_workers | 4 |
+------------------------+-------+
1 row in set (0.00 sec)
- 开始复制
start slave;
- 再次查看工作中线程,有了明显变化
show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 2 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 5 | system user | | NULL | Connect | 29 | Waiting for master to send event | NULL |
| 6 | system user | | NULL | Connect | 29 | Slave has read all relay log; waiting for more updates | NULL |
| 7 | system user | | NULL | Connect | 29 | Waiting for an event from Coordinator | NULL |
| 8 | system user | | NULL | Connect | 29 | Waiting for an event from Coordinator | NULL |
| 9 | system user | | NULL | Connect | 29 | Waiting for an event from Coordinator | NULL |
| 10 | system user | | NULL | Connect | 29 | Waiting for an event from Coordinator | NULL |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
测试主从服务
- 在主服务器上建toyota数据库
- 在重服务器上查看当前所有的库,可以看出,toyota库已经复制了过来。
show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| toyota |
+--------------------+
5 rows in set (0.01 sec)
到此mysql主从复制配置成功,后面如果有其他方面内容会继续更新…