MariaDB从复制和半复制

Mysql复制应用:
1,如何限制从服务器只读

read_only=ON

限制所有用户:

mysql > FLUSH TABLES WITH READ LOCK;

2,主从服务器的时间同步

*/5 * * * *  /usr/sbin/ntpdate cn.pool.ntp.org

3,如何主从复制时的事务安全
在主服务器配置:sync_binlog=1

主从复制配置:
1,双放版本一直,如果不一致则主要低于从
2,都从0开始复制,或者主服务器运行一段时间后,从服务器则需要备份后进行复制

配置过程
主服务器:
1,修改server-id,2,启动二进制日志

vim /etc/mysql/my.cnf
server-id       = 20
log-bin=/mydata/binlogs/master-bin  二进制日志位置

创建日志目录

mkdir /mydata/binlogs
chown mysql.mysql /mydata/binlogs

3,创建有复制权限的账号

MariaDB [(none)]>  GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'user'@'192.168.0.101' IDENTIFIED BY 'pass';

从服务器:
1,改server-id,2,启用中继日志

vim /etc/mysql/my.cnf
注释掉二进制日志:log-bin=/mydata/logs/slave-relay-bin
server-id       = 5
relay-log          = /mydata/relaylogs/relay-bin

创建目录

mkdir /mydata/relaylogs/
chown mysql.mysql /mydata/relaylogs/

3,指向主服务器,

3.1主服务器:MariaDB [(none)]> show master status;

+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000019 |     789  |              |                  |
+-------------------+----------+--------------+------------------+

CHANGE MASTER TO MASTER_HOST='192.168.0.101',MASTER_USER='user',MASTER_PASSWORD='pass',MASTER_LOG_FILE='master-bin.000019',MASTER_LOG_POS=789,MASTER_CONNECT_RETRY=5,MASTER_HEARTBEAT_PERIOD=2;

4,启动复制线程

MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.101
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 5
              Master_Log_File: master-bin.000019
          Read_Master_Log_Pos: 1603
               Relay_Log_File: mysql-slave-relay-bin.000003
                Relay_Log_Pos: 789
        Relay_Master_Log_File: master-bin.000019
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

主服务器运行一段时间后,从服务器则需要备份后进行复制
1,备份

[root@mysql-master local]# mysqldump --all-databases --flush-logs --master-data=2 --lock-all-tables > /tmp/`date +%F-%T`.sql

2,将备份传送到从服务器

[root@mysql-master local]# scp /tmp/2016-02-04-00\:34\:09.sql root@192.168.0.100:/tmp/

3,在从服务器上确保 Slave_IO_Running: No Slave_SQL_Running: No是停止的

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.0.101
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 5
              Master_Log_File: master-bin.000019
          Read_Master_Log_Pos: 1603
               Relay_Log_File: mysql-slave-relay-bin.000003
                Relay_Log_Pos: 798
        Relay_Master_Log_File: master-bin.000019
             Slave_IO_Running: No
            Slave_SQL_Running: No

4,恢复

[root@mysql-slave data]# mysql < /tmp/2016-02-04-00\:34\:09.sql 

5,打开/tmp/2016-02-04-00\:34\:09.sql 找到如下:

-- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000023', MASTER_LOG_POS=245;

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.0.101',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000023',MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.04 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.03 sec)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.101
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 5
              Master_Log_File: master-bin.000023
          Read_Master_Log_Pos: 245
               Relay_Log_File: mysql-slave-relay-bin.000002
                Relay_Log_Pos: 530
        Relay_Master_Log_File: master-bin.000023
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

到此为止恢复完成!

4,半同步复制
当数据到主服务器后会同步至从服务器,从服务器同步完成后才会向返回请求

主服务器启动插件

[root@mysql-master local]# mysql
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.09 sec)

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | OFF   |
| rpl_semi_sync_master_timeout       | 10000 | 等待响应时间
| rpl_semi_sync_master_trace_level   | 32    | 跟踪级别
| rpl_semi_sync_master_wait_no_slave | ON    | 
+------------------------------------+-------+
4 rows in set (0.00 sec)

MariaDB [(none)]> 

修改响应时间

MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_timeout =4000;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 4000  |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
4 rows in set (0.00 sec)

MariaDB [(none)]> 

从服务器启动插件

[root@mysql-slave plugin]# mysql
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | OFF   |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.01 sec)

MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.01 sec)

MariaDB [(none)]> 

主服务器插入数据:
MariaDB [(none)]> use mydb;
Database changed
首次执行需要2秒,rpl_semi_sync_master_wait_no_slave | ON 所定!时间由SET GLOBAL rpl_semi_sync_master_timeout =4000;所定!
MariaDB [linuxea]> CREATE TABLE t4(name char(20));
Query OK, 0 rows affected (4.04 sec)
MariaDB [mydb]>

关闭io进程后,在启动即可!

stop slave io_thread;
start slave io_thread;

在此插入即可!

MariaDB [linuxea]> CREATE TABLE t1(name char(20));
Query OK, 0 rows affected (0.07 sec)

MariaDB [linuxea]> CREATE TABLE t2(name char(20));
Query OK, 0 rows affected (0.02 sec)

MariaDB [linuxea]> CREATE TABLE t3(name char(20));
Query OK, 0 rows affected (0.08 sec)

MariaDB [linuxea]> 

主服务器验证版同步是否生效:| Rpl_semi_sync_master_clients | 1 |

MariaDB [linuxea]> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 539   |
| Rpl_semi_sync_master_net_wait_time         | 539   |
| Rpl_semi_sync_master_net_waits             | 1     |
| Rpl_semi_sync_master_no_times              | 1     |
| Rpl_semi_sync_master_no_tx                 | 3     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 443   |
| Rpl_semi_sync_master_tx_wait_time          | 443   |
| Rpl_semi_sync_master_tx_waits              | 1     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 1     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

MariaDB [linuxea]> 

一旦某次等待超时,会自动降级为异步!

2 分享

您可以选择一种方式赞助本站

支付宝扫码赞助

支付宝扫码赞助

日期: 2016-02-04分类: Mariadb

标签: mariadb

发表评论