MySQL主从复制
前言:为了解决Discuz论坛访问量大或者被攻击的情况
一、主MySQL设置
1.编辑主MYSQL 服务器的MySQL配置文件my.cnf,在mysqld下面添加以下参数:
log-bin=mysql-bin //开启MYSQL二进制日志 server-id=1 //服务器ID不能重复 binlog-do-db=dzx3 //需要做主从备份的数据库名字 expire-logs-days = 7 //只保留7天的二进制日志,以防磁盘被日志占满
注:如果您使用的是宝塔的MySQL,只需要在mysqld管理-配置修改中的[mysqld]中添加即可,在添加前,请检查上方4个配置项是否存在,如果存在则无需填写!
2.在主服务器添加一个用于主从复制的帐号:
登陆MySQL命令行(mysql -u root -p) 密码可在宝塔-数据库-root密码中查看
执行
create user '账号'@'从服务器IP' identified by '密码'; grant all privileges on *.* to '账号'@'从服务器IP' with grant option; revoke all privileges on *.* from '账号'@'从服务器IP';
IP可为非同一网络的服务器,但必须打开两边的数据库端口(3306)
3、重启MySQL ,让配置生效
systemctl restart mariadb
在主MySQL服务器上执行命令,把数据库设置成只读状态:
FLUSH TABLES WITH READ LOCK;
4、执行命令,并且记下file及position的值:
show master status;
(宝塔面板可以直接在当前状态中查看File与Position)
5、备份需要做主从备份的数据库,用导出成SQL或者直接复制数据库文件方式都可以
(宝塔面板直接备份数据库)
6、回到MYSQL命令行窗口,解封数据库只读状态,执行:
UNLOCK TABLES;
三、从MySQL服务器设置
1、编辑 从MYSQL服务器的MySQL配置文件my.cnf,在mysqld下面添加以下参数:
1 | server-id=2 //服务器ID不能重复 |
2、将主库上备份的数据库恢复到从库
(宝塔面板用导入功能导入刚刚下载的数据库备份文件)
3、重启从库MySQL
4、登录从库的MySQL命令行,执行:
change master to master_host='192.168.1.2', master_user='rep', master_password='123', master_log_file='file的值', master_log_pos=position的值;
//设置连接信息,file及position的值是之前记录下来,position的值没有单引号,其他的值要单引号
5、执行:
start slave; //启动从库连接
6、查看从库状态:
show slave status\G; //查看连接情况,是不是两个YES,两个YES为成功
注意:
1.检查主库和从库是不是有都有test数据库
没有的话就创建
CREATE DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
2.登录的账号需要设置授权访问
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION;
flush privileges;
刷新,不然不会生效
3.如果需要在从库上进行写操作,你可以设置从库为只读模式,以防止无意中对从库进行修改。在MySQL中,你可以使用以下命令将从库设置为只读模式:
SET GLOBAL read_only = ON;
刷新生效
这将确保从库不会执行写操作,从而保持数据的一致性。
重载my.cnf
mysqladmin --defaults-file=/etc/mysql/my.cnf reload -p
出现: Slave_IO_Running:Connecting
解决方法:关闭服务器服务器
测试
在master中的test库创建一个user表,有id和name两个字段
1 | CREATE TABLE user ( |
查看是否创建成功
1 | MariaDB [test]> show tables; |
测试 从MySQL是否同步
进入test库
use test
查看表
1 | MariaDB [test]> show tables; |
可以看到已经同步创建了user表了
我们在主MySQL中插入一条数据
1 | MariaDB [test]> INSERT INTO test.user (id, name) VALUES (1, 'John'); |
查看从MySQL中是否同步插入了
1 | MariaDB [test]> select * from user; |
可以看到已经同步插入了一条数据
修改Discuz配置文件
discuz的配置文件,config/config_global.php:
编辑
$_config['db']['common']['slave_except_table'] = '';
改为
$_config['db']['common']['slave_except_table'] = 'common_session,forum_attachment,forum_attachment_0,forum_attachment_1,forum_attachmen_9,forum_attachment_4,forum_attachment_2,forum_attachment_3,forum_attachment_5,forum_attachment_6,forum_attachment_7,forum_attachment_8';
(此处的attachment是在后期测试时发现远程附件的情况下使用主从会导致无法上传,您也可以尝试只填写)
$_config['db']['common']['slave_except_table'] = 'common_session';
然后在刚才编辑的那行下面加入从库配置:
1 | $_config['db']['1']['slave']['1']['dbhost'] = '192.168.1.2'; |
从库
1 | $_config['db']['1']['slave']['2']['dbhost'] = '192.168.1.3; |
也可以添加更多的从库
然后,如果有
$_config['db']['slave'] = '';
改为
$_config['db']['slave'] = 'true';
再进行测试,主从就完成啦
部分题外话:
如果数据库被攻击,从库占用cpu100%或者关闭,导致数据库没有正常执行同步指令,请不用担心,待从库正常后主库会自动尝试再次同步,您也可以断开网站与从库的链接加快这一步骤,另外,请确保主库不会死亡/重启,否者将无法正常同步,需要重新复制数据库至从库