Discuz! X3.x 如何配置数据库读写分离 降低服务器的负载

Discuz!X如何配置数据库读写分离主要分两个步骤MySQL主从设置、Discuz! Config设置。

一、MySQL主从设置

1、编辑主MYSQL 服务器的MySQL配置文件my.cnf，在[mysqld]下面添加以下参数：

1. log-bin=mysql-bin //开启MYSQL二进制日志
   
2. server-id=1 //服务器ID不能重
   
3. binlog-do-db=dzx2 //需要做主从备份的数据库名字

复制代码

2、在 A 服务器添加一个用于主从复制的帐号：
登陆mysql命令行，执行

1. GRANT REPLICATION SLAVE ON *.* TO '帐号'@'从服务器IP' IDENTIFIED BY '密码';

复制代码

例如：帐号是 rep，密码是 123，IP 是 192.168.1.3，则执行代码为

1. GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.1.3' IDENTIFIED BY '123';

复制代码

3、重启MySQL ，让配置生效
! v9 x7 e8 c9 P) Y9 r) U
二、关闭论坛访问，复制数据库备份到从库服务

1、登录论坛后台，“全局—站点信息”，关闭论坛访问
2、登录MySQL命令行，例如：

1. mysql -uroot –p

复制代码

3、在主MySQL服务器上执行命令，把数据库设置成只读状态：

1. FLUSH TABLES WITH READ LOCK;

复制代码

4、执行命令，并且记下file及position的值：

1. show master status;

复制代码

5、备份需要做主从备份的数据库，用导出成SQL或者直接复制数据库文件方式都可以
6、回到MYSQL命令行窗口，解封数据库只读状态，执行：

1. UNLOCK TABLES;

复制代码

7、登录论坛后台，“全局—站点信息”，开放论坛访
8、将刚才备份出来的数据复制到从库服务器

三、从MySQL服务器设置

1、编辑 从MYSQL服务器的MySQL配置文件my.cnf，在[mysqld]下面添加以下参数：

1. server-id=2 //服务器ID不能重复

复制代码

2、将主库上备份的数据库恢复到从库
3、重启从库MYSQL
4、登录从库的MySQL命令行，执行：

1. change master to master_host='192.168.1.2', master_user='rep', master_password='123', master_log_file='file的值', master_log_pos=position的值;
   
2. //设置连接信息，file及position的值是之前记录下来，position的值没有单引号，其他的值要单引号

复制代码

5、执行：

1. start slave; //启动从库连接

复制代码

6、查看从库状态：

1. show slave status\G; //查看连接情况，是不是两个YES，两个YES为成功

复制代码

7、编辑从MYSQL服务器的MySQL配置文件my.cnf，在[mysqld]下面添加以下参数：

1. master-host=192.168.1.2 //主库A的IP
   
2. master-user=rep //刚才在主库创建的帐号
   
3. master-password=123 //密码

复制代码

修改config.php 配置文件来配置读写主从关系
从Discuz! X2.5后系统就内置了读写分离的功能，方便一些负载比较大的站点使用,降低服务器的负载。但Discuz! X 2.5 默认未开启此功能，需要在配置文件中做相应的修改，此功能才会开启。下面介绍一下开启Discuz! X 2.5读写分离的设置方法，更高版本设置类似：

设置好MySQL主从关系后修改config/config_global.php配置文件
编辑

1. $_config['db']['common']['slave_except_table'] = '';

复制代码

改为

1. $_config['db']['common']['slave_except_table'] = 'common_session';

复制代码

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

1. $_config['db']['slave']['1']['dbhost'] = '192.168.1.2';
   
2. $_config['db']['slave']['1']['dbuser'] = 'root';
   
3. $_config['db']['slave']['1']['dbpw'] = 'password';
   
4. $_config['db']['slave']['1']['dbcharset'] = 'gbk';
   
5. $_config['db']['slave']['1']['pconnect'] = '0';
   
6. $_config['db']['slave']['1']['dbname'] = 'discuz';
   
7. $_config['db']['slave']['1']['tablepre'] = 'pre_';

复制代码

保存后即可生效

如果要添加多台从库，那么可以继续添加：

1. $_config['db']['slave']['2']['dbhost'] = '192.168.1.3;
   
2. $_config['db']['slave']['2']['dbuser'] = 'root';
   
3. $_config['db']['slave']['2']['dbpw'] = 'password';
   
4. $_config['db']['slave']['2']['dbcharset'] = 'gbk';
   
5. $_config['db']['slave']['2']['pconnect'] = '0';
   
6. $_config['db']['slave']['2']['dbname'] = 'discuz';
   
7. $_config['db']['slave']['2']['tablepre'] = 'pre_';

复制代码

mysql5.6+(5.6以上版本，比如5.7)主从设置 (mysql5.5之后的主从设置跟5.5之前的设置有一些差距)
怎么安装mysql数据库，这里不说了，只说它的主从复制，步骤如下
在进行主从设置之前
   首先确保mysql主从服务器之间的数据库端口防火墙互相打开，  
   尽量确保主从数据库账户一致性(主从切换使用)，否则将操作失败，  
   其次是确保mysql账户对mysql数据库目录有“可读写”权限非“可写”权限，  
   为了确保不出意外，最好删除mysql之前陈旧的mysql-bin、mysql日志，然后重启mysql  
  在主服务器上新建一个new_test数据库并为其建一个test表，然后导出导入到从服务器上(测试主从使用)服务器上可替换成现有的数据库进行操作（我这里主从上各建立一个相同账户和密码，相同的端口配置,为了主从切换，方便测试，记得防火墙端口和日志位置读写权限一定要有的）
    1、主从服务器分别作以下操作  ：  
   1.1、版本一致
   1.2、初始化表，并在后台启动mysql
   1.3、修改root的密码
   2、修改主服务器master:   
#vi /etc/my.cnf
  [mysqld]
log-bin=mysql-bin   //[必须]启用二进制日志
server-id=1       //[必须]默认是1，一般取IP最后一段
  port=1223
bing-address=0.0.0.0
log-bin=mysql-bin.log（必须，数据库日志文件，主从必须）
binlog-do-db =new_test (要记录的数据库,多个可换行多次设置)
replicate-do-db =new_test (要复制的数据库，多个可换行过个设置)
binlog-ignore-db=mysql //不对mysql库进行日志记录操作 如下意思雷同
binlog-ignore-db=test
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
replicate-ignore-db=test  //不对test进行复制操作 如下意思雷同
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
   3、修改从服务器slave:   
    #vi /etc/my.cnf
[mysqld]
port=1223
bing-address=0.0.0.0  //意思是允许所有 机器 服务器安全起见可设置为指定的服务器IP地址 如 116.128.1.10等
log-bin=mysql-bin.log
server-id=2
binlog-do-db =new_test
replicate-do-db =new_test
binlog-ignore-db=mysql
binlog-ignore-db=test
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
replicate-ignore-db=test
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
   4、重启两台服务器的mysql   
    service mysql restart

   5、在主服务器上建立帐户并授权slave:   
    #/usr/local/mysql/bin/mysql -u数据库账户名 -p数据库密码   
    mysql>GRANT REPLICATION SLAVE ON *.* to 'newback_username'@'%' identified by 'newback_pwd'; //一般不用root帐号，“%”表示所有客户端都可能连（安全起见可将%替换成指定服务器IP,如116.121.1.10），只要帐号，密码正确。
   6、登录主服务器的mysql，查询master的状态（可在phpmyadmin 中执行次操作）   
    mysql>show master status;
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000001 |      120 |              |                  |
    +------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)
    注：执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化
   7、配置从服务器Slave：   
mysql>change master to master_host='116.121.1.10',master_port=1223,master_user='newback',master_password='cctv@12315#$',master_log_file='mysql-bin.000001',master_log_pos=120   //注意不要断开，master_port为mysql服务器端口号(无引号)，master_user为执行同步操作的数据库账户，“120”无单引号(此处的120就是show master status 中看到的position的值，这里的mysql-bin.000001就是file对应的值)。(此处可在从服务器phpmyadmin中用sql语句操作)
Mysql>start slave;    //启动从服务器复制功能（可在phpmyadmin中执行该SQL语句）
   8、检查从服务器复制功能状态：   
    mysql> show slave statusG (可在从服务器phpmyadmin中执行“show slave status” SQL语句)
    *************************** 1. row ***************************
                 ……………………(省略部分)
               Slave_IO_Running: Yes       //此状态必须YES
              Slave_SQL_Running: Yes       //此状态必须YES
                 ……………………(省略部分)
注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。
以上操作过程，主从服务器配置完成。

   9、主从服务器测试：   
在主服务器new_test数据库中的test表中插入 或者更新一条记录，如果从服务器同样更新 插入 则配置正确，否则错误
  10、mysql5.5+版本与mysql5.5之前版本的一些差异：

其中大部分的内容相似
主要是5.5之后不再支持master打头的参数
如：
master-host，master-user，master-password，master-port等。
错误如下：
[ERROR] /usr/local/mysql/bin/mysqld: unknown variable 'master-host=192.168.2.182'
   主配置 不变，依旧是
  server-id=1
log-bin=log
binlog-do-db=database1          //需要同步的数据库
binlog-do-db=database2
binlog-ignore-db=mysql                     //被忽略的数据库
…………（省略部分）
  从配置 改为（注意下列注释部分，统统被废弃了）：
  server-id=2
#master-host=192.168.124.51
#master-user= AffairLog
#master-password= password
#master-port=3306
#master-connect-retry=60
replicate-do-db=database1     //同步的数据库
replicate-do-db=database2
replicate-ignore-db=mysql  //被忽略的数据库


其次是不能直接使用slave start 或者start slave 命令了，因为那样会报错，
  我们需要使用change master to
即：
  mysql>change master to
>master_host='192.168.124.51',
>master_user='username',
>master_password='password',
>master_log_file='bin-log.000001',
>master_log_pos=120;

然后start slave;
  (也可一句话执行如:"  change master to master_host='116.121.1.10',master_port=1223,master_user='newback',master_password='cctv@12315#$',master_log_file='mysql-bin.000001',master_log_pos=120" 【实际命令去掉外面的双引号，端口号和m aster_log_pos不加引号 】    )
其他一切不变

格林先生

MySQL 5.5.3 m3 主从同步不支持master-host问题的解决办法

格林先生

MySQL 5.7 主从数据库搭建

1.数据库安装

安装MySQL5.7，主数据库为192.168.2.221，从数据库为192.168.2.222，服务器内存8G

sudo apt-get install mysql-server
2.主数据库配置

修改/etc/mysql/my.cnf

#master

[mysqld]

#common
port = 3306
bind-address = 0.0.0.0
skip-name-resolve
max_connections = 512

#replicate
server_id = 1
#replicate database
binlog-do-db = test
log_bin = mysql-bin

#innodb
innodb_buffer_pool_size = 6G
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
进入数据库，添加同步用户

shell>mysql -uroot -p

mysql>create user ‘repl’@’%’ identified by ‘password’;
mysql>grant replication slave on * . * to ‘repl’@’%’;
记录master信息，File和Position

mysql>show master status\G
***************** 1. row *****************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB: test
Binlog_Ignore_DB:
Executed_Gtid_Set:
重启数据库

sudo /etc/init.d/mysql restart
3.从数据库配置

修改/etc/mysql/my.cnf

#slave

[mysqld]

#common
port = 3306
bind-address = 0.0.0.0
skip-name-resolve
max_connections = 512

#replicate
server_id = 2
#replicate database
replicate-do-db = test

#innodb
innodb_buffer_pool_size = 6G
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
配置slave

shell>mysql -uroot -p
mysql>change master to master_host =’192.168.2.221’,master_port=3306,master_user=’repl’,master_password=’password’,master_log_file=’mysql-bin.000001’,master_log_pos=154;
mysql>start slave;
重启数据库

shell>sudo /etc/init.d/mysql restart
shell>mysql -uroot -p
mysql>show slave status\G;
***************** 1. row *****************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.221
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 84848971
Relay_Log_File: database-2-relay-bin.000018
Relay_Log_Pos: 84849184
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 84848971
Relay_Log_Space: 84849443
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: afa582dc-2628-11e6-9226-408d5ce1124f
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
连接成功

4.批量插入性能

批量插入语句

insert into mt_data(mac,time,device_id,intensity,ap_mac,channel,virtual_type)values(……)*n
n为300时，每秒插入达到35000条数据

MySQL 5.7.13主从同步（复制）配置

主从同步是分布式MySQL数据库相当重要的配置，现在我在虚拟机上完成主从配置，系统是CentOS6.5，mysql版本是5.7.13

主服务器的ip是192.168.19.139 副服务器的ip是192.168.19.142

1.主服务器配置

(1)修改my.cnf(注意使用root)

vim /etc/my.cnf

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
log-bin=mysqlbin-log
server_id=1
basedir=/home/mysql
user=mysql
datadir=/home/mysql/data
port=3306
innodb_flush_log_at_trx_commit=1
sync_binlog=1
log-slave-updates

(2)进入mysql安装目录下的bin，启动mysql

cd /home/mysql/bin 2 ./mysqld

(3)重开启mysql客户端，进入mysql安装目录下的bin，授权副服务器主从复制，用mysqld的好处是可以查看错误。

./mysql -uroot -p 2 mysql> grant replication slave on *.* to [email protected] identified by '123';

(4)显示主服务器状态

mysql> show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| mysqlbin-log.000019 |    1290 |              |                  |                  |
+---------------------+----------+--------------+------------------+-------------------+

2.副服务器配置

(1)修改my.cnf(注意使用root)

vim /etc/my.cnf

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLE
user=mysql
server_id = 2
replicate-do-db = sbfxd
master-info-file = master.info
relay-log = relay-relay-bin
relay-log-index = relay-relay-bin.index
relay-log-info-file=relay-relay-log.info

(2)进入mysql安装目录下的bin，启动mysql

cd /home/mysql/bin 2 ./mysqld

(3)重开启mysql客户端，进入mysql安装目录下的bin，设置slave属性并开始服务

./mysql -uroot -p
mysql> change master to master_host='192.168.19.139',master_port= 3306, master_log_file='mysqlbin-log.000019', master_log_pos= 1290, master_bind='', master_user='test',master_password='123';
mysql> start slave;

(4)显示副服务器的状态

show slave status \G;

            Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.19.139
                  Master_User: test
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqlbin-log.000019
          Read_Master_Log_Pos: 1290
              Relay_Log_File: relay-relay-bin.000002
                Relay_Log_Pos: 323
        Relay_Master_Log_File: mysqlbin-log.000019
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: sbfxd
          Replicate_Ignore_DB:
          Replicate_Do_Table:
      Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                  Last_Errno: 0
                  Last_Error:
                Skip_Counter: 0
          Exec_Master_Log_Pos: 1290
              Relay_Log_Space: 530
              Until_Condition: None
              Until_Log_File:
                Until_Log_Pos: 0
          Master_SSL_Allowed: No
          Master_SSL_CA_File:
          Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
              Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
              Last_SQL_Errno: 0
              Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
            Master_Server_Id: 1
                  Master_UUID: 05408021-5ab2-11e6-869b-000c293990c1
            Master_Info_File: /home/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
          Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
    Last_SQL_Error_Timestamp:
              Master_SSL_Crl:
          Master_SSL_Crlpath:
          Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
        Replicate_Rewrite_DB:
                Channel_Name:
          Master_TLS_Version:

出现两个yes表示slave可以跑了

3.注意事项：

(1)楼主的虚拟机是通过克隆产生的，所以不需要对mysql进行备份，然后在副服务器上恢复，这步对于数据库极其重要，否则就会出现如下错误:

2016-08-17T03:11:27.579193Z 5 [ERROR] Slave SQL for channel '': Error executing row event: 'Table 'sbfxd.sbtable' doesn't exist', Error_code: 1146
2 2016-08-17T03:11:27.584268Z 5 [Warning] Slave: Table 'sbfxd.sbtable' doesn't exist Error_code: 1146

(2)如果将主服务器的属性配错了需要stop slave，然后设置好之后start slave



注释：按照文章配置由于使用的是虚拟机在配置slave的时候是直接复制的虚拟机，配好启动发下show slave status \G；

     Slave_IO_Running: NO
            Slave_SQL_Running: Yes
Last_IO_Error：显示意思是master 和 slave uuid 重复.忘了截图了。大体这个意思，因为slave是master虚拟机复制的 mysql的data目录里auto.cnf中server-uuid一致，修改一个slave的uuid，重启可以了。