mysql数据库主从模式配置

fengda

　　mysql主从模式：
　　主从复制：
　　从服务器：
　　I/O线程：从master请求二进制日志信息，并保存至中继日志；
　　SQL线程：从relay log中读取日志信息，在本地完成重放；
　　配置过程：
　　1、master
　　(1) 启用二进制日志；
　　(2) 设置一个在当前集群中惟一的server-id；
　　(3) 创建一个有复制权限(REPLICATION SLAVE, REPLICATION CLIENT)账号；
　　2、slave
　　(1) 启用中继日志；
　　(2) 设置一个在当前集群中惟一的server-id；
　　(3) 使用有复制权限用户账号连接至主服务器，并启动复制线程；
　　示例：
　　环境为CentOS7.2,mariadb-server-5.5.52-1.el7.x86_64
　　主服务器：
　　[root@node3 /]# vim /etc/my.cnf
　　log-bin=master-bin###启用二进制日志
　　server-id=1###设置server-id
　　skip_name_resolve = ON
　　innodb_file_per_table = ON
　　[root@node3 /]# systemctl start mariadb.service###启动服务
　　MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%log%';###进入mysql查看配置
　　log_bin                                   | ON
　　MariaDB [(none)]> SHOW MASTER LOGS;
　　+-------------------+-----------+
　　| Log_name          | File_size |
　　+-------------------+-----------+
　　| master-bin.000001 |       264 |
　　| master-bin.000002 |       245 |
　　MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%server%';###查看server-id
　　+----------------------+-------------------+
　　| Variable_name        | Value             |
　　+----------------------+-------------------+
　　| character_set_server | latin1            |
　　| collation_server     | latin1_swedish_ci |
　　| server_id            | 1                 |
　　+----------------------+-----------------
　　MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168

　　.%.%'>　　###创建一个有复制权限(REPLICATION SLAVE, REPLICATION CLIENT)账号
　　MariaDB [(none)]> FLUSH PRIVILEGES;###主服务器配置完成
　　Query OK, 0 rows affected (0.00 sec)
　　从服务器：
　　[root@localhost ~]# vim /etc/my.cnf
　　skip_name_resolve=ON
　　innodb_file_per_table=ON
　　relay-log=relay-log###启用中继日志
　　relay-log-index=relay-log.index                ###设置中继日志索引文件名
　　server-id=2        ###设置server-id
　　[root@localhost ~]# systemctl start mariadb.service
　　MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%log%';###查看中继日志

　　|>
　　relay_log_index                           |>　　MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%server%';
　　+----------------------+-------------------+
　　| Variable_name        | Value             |
　　+----------------------+-------------------+
　　| character_set_server | latin1            |
　　| collation_server     | latin1_swedish_ci |
　　| server_id            | 2                 |###查看server-id
　　###查看主服务器节点的位置
　　MariaDBMariaDB [(none)]> SHOW MASTER LOGS;
　　+-------------------+-----------+
　　| Log_name          | File_size |
　　+-------------------+-----------+
　　| master-bin.000001 |       264 |
　　| master-bin.000002 |       264 |
　　| master-bin.000003 |       498 |
　　+-------------------+-----------+###位置为498
　　MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.0.188', MASTER_USER='repluser', MA
　　STER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=498;Query OK, 0 rows affected (0.05 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.188
　　Master_User: repluser
　　Master_Port: 3306
　　Connect_Retry: 60
　　Master_Log_File: master-bin.000003
　　Read_Master_Log_Pos: 498

　　Relay_Log_File:>
　　>
　　>　　Slave_IO_Running: Yes###IO线程启动
　　Slave_SQL_Running: Yes###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: 498

　　>　　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 [(none)]> CREATE DATABASE mydb;###主节点创建一个库
　　Query OK, 1 row affected (0.00 sec)
　　MariaDB [(none)]> SHOW MASTER LOGS;
　　+-------------------+-----------+
　　| Log_name          | File_size |
　　+-------------------+-----------+
　　| master-bin.000001 |       264 |
　　| master-bin.000002 |       264 |
　　| master-bin.000003 |       581 |###查看二进制日志位置在581
　　在从节点查看：
　　MariaDB [(none)]> SHOW SLAVE STATUS\G
　　*************************** 1. row ***************************
　　Slave_IO_State: Waiting for master to send event
　　Master_Host: 192.168.0.188
　　Master_User: repluser
　　Master_Port: 3306
　　Connect_Retry: 60
　　Master_Log_File: master-bin.000003
　　Read_Master_Log_Pos: 581###位置为581

　　Relay_Log_File:>
　　>
　　>　　Slave_IO_Running: Yes
　　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: 1007
　　Last_Error: Error 'Can't create database 'mydb'; database exists' on qu
　　ery. Default database: 'mydb'. Query: 'CREATE DATABASE mydb'                 Skip_Counter: 0
　　Exec_Master_Log_Pos: 498

　　>　　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: 1007
　　Last_SQL_Error: Error 'Can't create database 'mydb'; database exists' on qu
　　ery. Default database: 'mydb'. Query: 'CREATE DATABASE mydb'  Replicate_Ignore_Server_Ids:
　　Master_Server_Id: 1
　　1 row in set (0.00 sec)
　　MariaDB [(none)]> SHOW DATABASES;
　　+--------------------+
　　| Database           |
　　+--------------------+
　　| information_schema |
　　| hellodb            |
　　| mydb               |###显示mydb同步到从节点
　　| mysql              |
　　| performance_schema |
　　| test               |
　　+--------------------+
　　6 rows in set (0.00 sec)
　　另外：复制架构中应该注意的问题：
　　1.限制从服务器为只读
　　在从服务器上设置read_only=ON,此限制对拥有SUPER权限的用户均无效
　　阻止所有用户：
　　mysql> FLUSH TABLES　WITH READ LOCK;     ###启动一个连接线程后不退出终端，所有写操作被阻塞，只能读操作。
　　2.保证主从复制的事务安全
　　在master节点启用参数：
　　sync_binlog=ON
　　###事务提交时，将binlog缓冲区的事件立即同步至磁盘中二进制日志文件中。
　　如果用到的为InnoDB存储引擎：
　　innodb_flush_logs_at_trx_commit=ON  ###事务提交时立即将缓冲区与事务相关的数据刷写至事务日志中
　　innodb_support_xa=ON###分布式事务
　　在slave节点：
　　skip_slave_start=ON   ###从节点开机不自动启动复制线程（检测无误后手动启动）
　　mster节点：
　　sync_master_info###同步master_info信息至磁盘
　　slave节点：
　　sync_relay_log###同步relay_log信息至磁盘
　　sync_relay_log_info
　　如果主节点已经运行一段时间，且有大量数据时，配置slave节点：
　　通过备份恢复数据至从服务器
　　复制起始位置为备份时二进制日志文件及其POS。