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 statusG
*************************** 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 statusG
*************************** 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 statusG
*************************** 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]> 

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