mysql主从复制

KevinZ_1992

于 2018-10-18 16:22:00 发布

阅读量158

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/forlive/p/9811274.html

版权

1.编辑主库配置文件，添加如下内容

[root@localhost pub]# vi /etc/my.cnf
[mysqld]
log_bin=master-bin        #开启二进制日志，并给二进制日志文件起名
server_id=1                     #配置服务器节点id，全局唯一
innodb_file_per_table=ON      #开启每表单独存储
skip_name_resolve=on           #关闭域名反解

2.查看二进制日志开启情况

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |
| log_bin_trust_function_creators | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
3 rows in set (0.00 sec)

MariaDB [(none)]> SHOW MASTER LOGS;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |     30379 |
| master-bin.000002 |   1038814 |
| master-bin.000003 |       245 |
+-------------------+-----------+
3 rows in set (0.00 sec)

　3.查看服务id

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

4.创建一个拥有复制权限的账号，并将主库锁定，不能写入　

MariaDB [mysql]> GRANT REPLICATION SLAVE , REPLICATION CLIENT ON *.* TO copy@'%' IDENTIFIED BY 'copy';
MariaDB [mysql]> FLUSH PRIVILEGES ;
MariaDB [mysql]> FLUSH tables with read lock;

4.1在主库中备份出所有数据，在从库上恢复

#主库备份
[root@localhost ~]# mysqldump -uroot -p 数据库名称 > /root/备份库位置
Enter password: 


#从库恢复
[root@localhost ~]# mysql -uroot -p 数据库名称 < 数据库备份文件
Enter password:

5.编辑从库配置文件

[root@back ~]# vi /etc/my.cnf
relay_log=relay-log         #开启中继日志
server_id=2                      #配置服务器节点id，全局唯一
innodb_file_per_table=ON          #开启每表单存储
skip_name_resolve=ON             #关闭域名反解

6.查看中继日志是否开启

MariaDB [mysql]> SHOW GLOBAL VARIABLES LIKE '%relay_log';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| innodb_recovery_update_relay_log | OFF       |
| relay_log                        | relay-log |
| sync_relay_log                   | 0         |
+----------------------------------+-----------+
3 rows in set (0.00 sec)

7.查看server_id

MariaDB [mysql]> SHOW GLOBAL VARIABLES LIKE '%server_id%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.00 sec)

8.设置主库位置及其他信息

MariaDB [hellodb]> CHANGE MASTER TO 
    -> MASTER_HOST='172.17.148.255' ,     #主库地址
    -> MASTER_USER='copy' ,                      #复制用户
    -> MASTER_PASSWORD='copy' ,           #用户密码
    -> MASTER_LOG_FILE='master-bin.000003' ,     #从哪个二进制文件开始复制
    -> MASTER_LOG_POS=245;                   #复制起始位置

9.开启复制线程

MariaDB [mysql]> START SLAVE ;

10.查看SLAVE状态

MariaDB [mysql]> SHOW SLAVE STATUS \G

11.主库解锁

MariaDB [mysql]> UNLOCK TABLES;

复制架构中应该注意的问题

1.限制从服务器为只读　

在从服务器上限制read_only=ON，此限制对拥有super权限的用户均无效

MariaDB [hellodb]> SET GLOBAL read_only=ON;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

或是在配置文件中添加此项

[root@back ~]# vi /etc/my.cnf
read_only=ON

2.如何保证主从复制的事务安全

在master节点启用参数

sync_binlog=ON   #事务提交时，立即将内存中的二进制文件写入磁盘
sync_master_info=ON     #马上同步这个文件内容至磁盘
innodb_flush_logs_at_trx_commit=ON   #提交时，立即将日志写入磁盘
innodb_support_xa=ON     #支持分布式

在slave节点启用参数

skip_slave_start=ON       #关闭自动提交
sync_relay_log=ON          #文件立即同步至磁盘
sync_relay_log_info=ON    #立即同步至磁盘

*如果从库之前做过复制，需要清空之前的配置

#先停止复制
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

#再清空状态
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)

#重新配置
mysql> CHANGE MASTER TO \
    -> MASTER_HOST='10.69.39.73',\
    -> MASTER_USER='copy',\
    -> MASTER_PASSWORD='copy',\
    -> MASTER_LOG_FILE='mysql-bin.000002',\
    -> MASTER_LOG_POS=106;
Query OK, 0 rows affected (0.01 sec)

#开启复制
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

*查看主从状态

#主库的Position不能为0
mysql> SHOW MASTER STATUS \G
*************************** 1. row ***************************
            File: mysql-bin.000002
        Position: 106
    Binlog_Do_DB: gz_namc
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)


#从库的Slave_IO_Running，Slave_SQL_Running要为yes

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.69.39.73
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 106
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

*自动删除之前的二进制日志

#设置最多保留多长时间之前的日志
mysql> set GLOBAL expire_logs_days=30;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 30    |
+------------------+-------+
1 row in set (0.00 sec)

转载于:https://www.cnblogs.com/forlive/p/9811274.html