mysql主从复制（添加从服务器）和双主复制

思路：
master:
     1、启用二进制日志
     2、定义 server-id
     3、创建有复制权限的账户
     完善设置
     4、设置事务日志从内存缓冲直接同步存储到磁盘中
slave：
     1、启用中继日志
     2、定义server-id
     3、使用有复制权限的帐号连接master
     4、启动io thread以及sql thread
     完善设置
     5、定义节点为只读数据库

MariaDB复制操作（从零开始）
环境：
    vm1-master:IP 172.16.3.2/16
    vm2-slave: IP 172.16.3.10/16

1、mariadb安装(master节点配置)
      # mkdir -pv /mydata/data
      # tar xf mariadb-5.5.36-linux-x86_64.tar.gz -C /usr/local
      # cd /usr/local
      # ln -sv mariadb-5.5.36-linux-x86_64 mysql
      # useradd -r mysql
      # chown -R mysql.mysql /mydata/data/
      # cd mysql/
      # chown -R root.mysql ./*
      # mkdir /etc/mysql      
      # cp support-files/my-large.cnf /etc/mysql/my.cnf
      # vim /etc/mysql/my.cnf
    [mysqld]
    port            = 3306
    socket          = /tmp/mysql.sock
    skip-external-locking
    key_buffer_size = 256M
    max_allowed_packet = 1M
    table_open_cache = 256
    sort_buffer_size = 1M
    read_buffer_size = 1M
    read_rnd_buffer_size = 4M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 8
    query_cache_size= 16M
    # Try number of CPU‘s*2 for thread_concurrency
    thread_concurrency = 8
    datadir = /mydata/data   添加数据存储目录
    sync_binlog=1           设置事务日志从内存缓冲直接同步存储到磁盘中

    # cd /usr/local
    # cp  support-files/mysql.server /etc/rc.d/init.d/mysqld
    # chmod +x /etc/rc.d/init.d/mysqld
    # chkconfig --add mysqld
    # scripts/mysql_install_db --datadir=/mydata/data/ --user=mysql

    # mkdir -pv /mydata/binlogs/
    # chown -R mysql.mysql /mydata/binlogs
    # vim /etc/mysql/my.cnf
    log-bin         = /mydata/binlogs/master-bin     指定二进制的存放位置，和名称
    binlog_format   = mixed                     开启 记录格式
    server-id       = 1                            server-id 等于1
    # service mysqld start

    # vim /etc/profile.d/mysql.sh
    export PATH=/usr/local/mysql/bin:$PATH
    # source /etc/profile.d/mysql.sh

2、创建有复制权限的帐号

    # mysql
    MariaDB [(none)]> grant replication slave,replication client on *.* to ning@‘172.16.3.10‘identified by ‘ning‘;
    Query OK, 0 rows affected (0.04 sec)

    MariaDB [(none)]> flush privileges;
    Query OK, 0 rows affected (0.00 sec)

    MariaDB [(none)]> show master status;                  查看二进制位置
    +-------------------+----------+--------------+------------------+
    | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +-------------------+----------+--------------+------------------+
    | master-bin.000001 |      486 |              |                  |
    +-------------------+----------+--------------+------------------+
    1 row in set (0.02 sec)

mariadb安装(从slave节点配置)
1、安装
     # mkdir -pv /mydata/data
      # tar xf mariadb-5.5.36-linux-x86_64.tar.gz -C /usr/local
      # cd /usr/local
      # ln -sv mariadb-5.5.36-linux-x86_64 mysql
      # useradd -r mysql
      # chown -R mysql.mysql /mydata/data/
      # cd mysql/
      # chown -R root.mysql ./*
      # mkdir /etc/mysql      
      # cp support-files/my-large.cnf /etc/mysql/my.cnf
      # vim /etc/mysql/my.cnf
    [mysqld]
    port            = 3306
    socket          = /tmp/mysql.sock
    skip-external-locking
    key_buffer_size = 256M
    max_allowed_packet = 1M
    table_open_cache = 256
    sort_buffer_size = 1M
    read_buffer_size = 1M
    read_rnd_buffer_size = 4M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 8
    query_cache_size= 16M
    # Try number of CPU‘s*2 for thread_concurrency
    thread_concurrency = 8
    datadir = /mydata/data   添加数据存储目录
    read_only = 1  启动从节点是只读（不允许写入数据）

    # cd /usr/local
    # cp  support-files/mysql.server /etc/rc.d/init.d/mysqld
    # chmod +x /etc/rc.d/init.d/mysqld
    # chkconfig --add mysqld
    # scripts/mysql_install_db --datadir=/mydata/data/ --user=mysql

    # mkdir -pv /mydata/relaylogs/
    # chown -R mysql.mysql /mydata/relaylogs
    # vim /etc/mysql/my.cnf
    #log-bin=mysql-bin                                  不记录二进制日志
    #binlog_format=mixed                                不开启日志记录格式
    server-id       = 11                                server-id 和主节点的server-id不同
    relay-log       = /mydata/relaylogs/relay-log   设置中继日志文件的存放路径
    # service mysqld start

    # vim /etc/profile.d/mysql.sh
    export PATH=/usr/local/mysql/bin:$PATH
    # source /etc/profile.d/mysql.sh

2、连接主节点服务器
    # mysql
    MariaDB [(none)]> change master to master_host=‘172.16.3.2‘, master_user=‘ning‘, master_password=‘ning‘, master_log_file=‘master-bin.000001‘, master_log_pos=486;
    Query OK, 0 rows affected (0.05 sec)

    # ls /mydata/relaylogs/              查看下中继日志中是否有从主节点复制过来的二进制日志文件
    relay-log.000001  relay-log.index
    MariaDB [(none)]> show slave status\G            查看从节点信息
    *************************** 1. row ***************************
                   Slave_IO_State:
                      Master_Host: 172.16.3.2
                      Master_User: ning
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: master-bin.000001
              Read_Master_Log_Pos: 486
                   Relay_Log_File: relay-log.000001
                    Relay_Log_Pos: 4
            Relay_Master_Log_File: master-bin.000001    正在中继的日志文件是什么
                 Slave_IO_Running: No                  Slave_IO线程没有启动
                Slave_SQL_Running: No                   Slave_SQL线程没有启动
                  Replicate_Do_DB:
              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: 486
                  Relay_Log_Space: 245
                  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: NULL
    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: 0
    1 row in set (0.00 sec)

3、启动线程IO_THREAD和SQL_THREAD

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

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

    MariaDB [(none)]> show slave status\G       查看启动结果
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 172.16.3.2
                      Master_User: ning
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: master-bin.000001
              Read_Master_Log_Pos: 486
                   Relay_Log_File: relay-log.000002
                    Relay_Log_Pos: 530                中继日志的同步过来了
            Relay_Master_Log_File: master-bin.000001
                 Slave_IO_Running: Yes               这里可以看到是启动
                Slave_SQL_Running: Yes                 这里可以看到是启动
                  Replicate_Do_DB:
              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: 486
                  Relay_Log_Space: 818
                  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
    1 row in set (0.00 sec)

主从复制的测试：

我们在主节点上创建个数据看是否能复制过来

1、创建数据：
    MariaDB [(none)]> create database ning;
    Query OK, 1 row affected (0.00 sec)

    MariaDB [(none)]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | ning               |
    | performance_schema |
    | test               |
    +--------------------+
    5 rows in set (0.06 sec)

2、从节点上查看数据是否存在
    MariaDB [(none)]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | ning               |----------------------是存在的
    | performance_schema |
    | test               |
    +--------------------+
    5 rows in set (0.05 sec)

    MariaDB [(none)]> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 172.16.3.2
                      Master_User: ning
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: master-bin.000001
              Read_Master_Log_Pos: 569                这里可以看到，从主节点复制的二进制日志位置已经到了549
                   Relay_Log_File: relay-log.000002
                    Relay_Log_Pos: 613                
            Relay_Master_Log_File: master-bin.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB:
              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: 569
                  Relay_Log_Space: 901
                  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
    1 row in set (0.00 sec)

管理命令:
1、停止从主节点复制
    MariaDB [(none)]> stop slave;
    Query OK, 0 rows affected (0.14 sec)

    MariaDB [(none)]> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State:
                      Master_Host: 172.16.3.2
                      Master_User: ning
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: master-bin.000001
              Read_Master_Log_Pos: 569
                   Relay_Log_File: relay-log.000002
                    Relay_Log_Pos: 613
            Relay_Master_Log_File: master-bin.000001
                 Slave_IO_Running: No                 这两个线程都停止了
                Slave_SQL_Running: No
                  Replicate_Do_DB:
              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: 569
                  Relay_Log_Space: 901
                  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: NULL
    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
    1 row in set (0.00 sec)

2、启动从主节点复制
    MariaDB [(none)]> start slave;
    Query OK, 0 rows affected (0.00 sec)

    MariaDB [(none)]> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 172.16.3.2
                      Master_User: ning
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: master-bin.000001
              Read_Master_Log_Pos: 569
                   Relay_Log_File: relay-log.000003
                    Relay_Log_Pos: 530
            Relay_Master_Log_File: master-bin.000001
                 Slave_IO_Running: Yes                  这两个线程是开启的。
                Slave_SQL_Running: Yes
                  Replicate_Do_DB:
              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: 569
                  Relay_Log_Space: 1186
                  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
    1 row in set (0.00 sec)

=====================================================================================================================
MariaDB复制操作（不是从零开始添加从节点）

环境：
    和上面的基础环境一致

思路：
    master:
          执行完全备份
    slave：
          1、导入完全备份
          2、连接master，并指定的从完全备份的结束日志位置开始，复制二进制日志到，中继日志中。
          3、开启从节点
master节点配置
1、查看下现在的主节二进制日志位置
    MariaDB [ning]> show master status;
    +-------------------+----------+--------------+------------------+
    | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +-------------------+----------+--------------+------------------+
    | master-bin.000003 |     8096 |              |                  |
    +-------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)

2、完整备份
    # vim /etc/profile.d/mysqldump.sh              由于是编译安装，mysqldump需要设置路径
    export PATH=/usr/local/mysql/bin:$PATH
    # source /etc/profile.d/mysqldump.sh

    # mysqldump --all-databases --lock-all-tables --master-data=2 > all.sql   完全备份整个数据库

    # cat all.sql
    -- CHANGE MASTER TO MASTER_LOG_FILE=‘master-bin.000003‘, MASTER_LOG_POS=8096;   查看完全备份的二进制日志的结束位置；

3、传送完整备份到从节点
    # scp all.sql 172.16.3.10:/root/

slave从节点配置
1、把完全备份导入到数据库中
    # mysql < all.sql

2、启动连接主服务器
    MariaDB [(none)]> change master to master_host=‘172.16.3.2‘, master_user=‘ning‘, master_password=‘ning‘ , master_log_file=‘master-bin.000003‘, master_log_pos=8096;
    Query OK, 0 rows affected (0.03 sec)

    MariaDB [(none)]> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State:
                      Master_Host: 172.16.3.2
                      Master_User: ning
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: master-bin.000003
              Read_Master_Log_Pos: 8096                 查看是否和完全备份的二进制日志位置号相同（如果相同就可以启动从节点了）
                   Relay_Log_File: relay-log.000001
                    Relay_Log_Pos: 4
            Relay_Master_Log_File: master-bin.000003
                 Slave_IO_Running: No
                Slave_SQL_Running: No
                  Replicate_Do_DB:
              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: 8096
                  Relay_Log_Space: 245
                  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: NULL
    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: 0
    1 row in set (0.00 sec)

3、开启从节点（如果没有安装两线程，需要安装在启动；安装上面实例中有）
    MariaDB [(none)]> start slave;           启动从节点从主节点复制
    Query OK, 0 rows affected (0.00 sec)

    MariaDB [(none)]> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 172.16.3.2
                      Master_User: ning
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: master-bin.000003
              Read_Master_Log_Pos: 8337
                   Relay_Log_File: relay-log.000002
                    Relay_Log_Pos: 771
            Relay_Master_Log_File: master-bin.000003
                 Slave_IO_Running: Yes                    （看这里是否启动正常）
                Slave_SQL_Running: Yes
                  Replicate_Do_DB:
              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: 8337
                  Relay_Log_Space: 1059
                  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
    1 row in set (0.00 sec)

====================================================================================
环境：
    vm1-master:IP 172.16.3.2/16
    vm2-slave: IP 172.16.3.10/16
时间必须同步：
思路：

    双主模型                   
             1、双主节点上都得创建具有复制权限的用户
             2、双节点都得启动中继日志和二进制日志
             3、为保证具有自动增长功能的字段能正确生成ID，需要配置两个节点分别使用偶数或奇数ID号
             4、都需要配置为自己的主节点
master1主配置    
1、
# mkdir /mydata/{data binlogs relaylogs}

2、配置文件
# vim /etc/mysql/my.cnf
[mysqld]
port        = 3306
socket        = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M

thread_concurrency = 8
datadir = /mydata/data       指定数据目录

#tmpdir        = /tmp/

log-bin=/mydata/binlogs/master1-bin     指定二进制文件的存储位置
relay-log=/mydata/relaylogs/relay-log    指定中继日志的存放位置

binlog_format=mixed                      指定记录二进制日志的格式

server-id    = 1                              serverID
auto-increment-offset = 1                   开始ID为1
auto-increment-increment = 2                每次偏移2

# service mysqld start

3、创建具有复制权限的用户
# mysql
MariaDB [(none)]> grant replication slave,replication client on *.* to ning@‘172.16.3.10‘identified by ‘ning‘;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

4、查看当前二进制日志的位置
MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| master1-bin.000001 |      496 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

5、连接master1节点进行复制
MariaDB [(none)]> change master to master_host=‘172.16.3.10‘, master_user=‘ning‘, master_password=‘ning‘, master_log_file=‘master2-bin.000001‘, master_log_pos=495;
Query OK, 0 rows affected (0.13 sec)

master2主配置文件

1、
# mkdir /mydata/{data binlogs relaylogs}

2、主配置文件
# vim /etc/mysql/my.cnf
[mysqld]
port        = 3306
socket        = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M

thread_concurrency = 8
datadir = /mydata/data       指定数据目录

#tmpdir        = /tmp/

log-bin=/mydata/binlogs/master2-bin     指定二进制文件的存储位置
relay-log=/mydata/relaylogs/relay-log    指定中继日志的存放位置

binlog_format=mixed                      指定记录二进制日志的格式

server-id    = 11                              serverID
auto-increment-offset = 2                   开始ID为2
auto-increment-increment = 2                每次偏移2

# service mysqld start

3、创建具有复制权限的用户
# mysql
MariaDB [(none)]> grant replication slave,replication client on *.* to ning@‘172.16.3.2‘identified by ‘ning‘;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

4、查看当前二进制日志的位置

MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| master2-bin.000001 |      495 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

5、连接master1节点进行复制
MariaDB [(none)]> change master to master_host=‘172.16.3.2‘, master_user=‘ning‘, master_password=‘ning‘, master_log_file=‘master1-bin.000001‘, master_log_pos=496;
Query OK, 0 rows affected (0.13 sec)

测试：双方服务器都启动服务start slave

双方创建数据，查看是否都能看到对方的数据