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 下面添加以下参数：

    server-id=2 //服务器ID不能重复
    replicate-do-db=test //需要做复制的数据库名
    replicate-ignore-table=test.pre_common_session //自动跳过的表，session表没必要做复制
    slave-skip-errors = 1032,1062,126,1114,1146,1048,1396 //自动跳过的错误代码，以防复制出错被中断

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 statusG; //查看连接情况，是不是两个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 两个字段

 CREATE TABLE user (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     name VARCHAR(50) NOT NULL
    -> );

查看是否创建成功

MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user           |
+----------------+
1 row in set (0.00 sec)

测试 从 MySQL 是否同步

进入 test 库

use test

查看表

MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user           |
+----------------+
1 row in set (0.00 sec)

可以看到已经同步创建了 user 表了

我们在主 MySQL 中插入一条数据

MariaDB [test]> INSERT INTO test.user (id, name) VALUES (1, 'John');
Query OK, 1 row affected (0.01 sec)

查看从 MySQL 中是否同步插入了

MariaDB [test]> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | John |
+----+------+
1 row in set (0.00 sec)

可以看到已经同步插入了一条数据

修改 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';

然后在刚才编辑的那行下面加入从库配置：

$_config['db']['1']['slave']['1']['dbhost'] = '192.168.1.2';

$_config['db']['1']['slave']['1']['dbuser'] = 'root';

$_config['db']['1']['slave']['1']['dbpw'] = 'password';

$_config['db']['1']['slave']['1']['dbcharset'] = 'utf8';

$_config['db']['1']['slave']['1']['pconnect'] = '0';

$_config['db']['1']['slave']['1']['dbname'] = 'discuz';

$_config['db']['1']['slave']['1']['tablepre'] = 'pre_';

$_config['db']['1']['slave']['1']['weight'] = '1'; //从库权重，越大分配率越高

从库

$_config['db']['1']['slave']['2']['dbhost'] = '192.168.1.3;
$_config['db']['1']['slave']['2']['dbuser'] = 'root';
$_config['db']['1']['slave']['2']['dbpw'] = 'password';
$_config['db']['1']['slave']['2']['dbcharset'] = 'utf8';
$_config['db']['1']['slave']['2']['pconnect'] = '0'; //不同局域网的机子建议开启持久链接，加快网页加载
$_config['db']['1']['slave']['2']['dbname'] = 'discuz';
$_config['db']['1']['slave']['2']['tablepre'] = 'pre_';
$_config['db']['1']['slave']['2']['weight'] = '1';

也可以添加更多的从库

然后，如果有

$_config['db']['slave'] = '';

改为

$_config['db']['slave'] = 'true';

再进行测试，主从就完成啦

部分题外话：
如果数据库被攻击，从库占用 cpu100%或者关闭，导致数据库没有正常执行同步指令，请不用担心，待从库正常后主库会自动尝试再次同步，您也可以断开网站与从库的链接加快这一步骤，另外，请确保主库不会死亡/重启，否者将无法正常同步，需要重新复制数据库至从库