MySQL 的主从复制
所有的关系型数据库都存在一个通病性能差, 在企业中如果用户量特别打, 将所有的数据都存放在一台服务器上, 其性能时远远达不到要求的. 所以需要使用一些手段来解决其性能的问题.
提升性能的方式有向上扩展以及向外扩展
向上扩展(Scale Up): 使用更新更好的硬件, 但硬件在怎么更新也有其性能的极限. 盲目的向上扩展无法结局根本的问题
向外扩展(Scale Out): 就是使用多台机器分摊压力来提供服务
主从复制就是拿多个数据库服务器, 组合成一个服务器的集合对外共同服务实现性能的提升, 逻辑上使用的时对外扩展的方式 (Scale out) 来提升服务器的性能.
新主机搭建主从复制搭建
服务器类型 | ip 地址 |
---|---|
主 | 192.168.73.133 |
从 | 192.168.73.145 |
主服务器操作
1. 在主服务器上启用二进制日志
- [root@localhost ~]# VIM /etc/MySQL/my.cnf
- log-bin=/data/bin/MySQL-bin
- binlog-format=row
- server-id=1
2. 创建二进制日志目录
- [root@localhost ~]# mkdir /data/bin
- [root@localhost ~]# chown -R MySQL.MySQL /data/bin
3. 重启服务
- [root@localhost ~]# service mysqld restart
- Restarting mysqld (via systemctl): [ OK ]
4. 创建一个用来让从服务器复制数据的账号
- MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.73.%' IDENTIFIED BY 'CentOS';
- Query OK, 0 rows affected (0.00 sec)
5. 查看主服务器正在使用的二进制日志
- MariaDB [(none)]> SHOW MASTER LOGS;
- +------------------+-----------+
- | Log_name | File_size |
- +------------------+-----------+
- | MySQL-bin.000001 | 515 | #当前二进制文件及位置需要记录, 从服务器设置时需要
- +------------------+-----------+
- 1 row in set (0.00 sec)
从服务器操作
1. 修改配置文件
- server-id = 2 #server-id 改为和主服务器不同
- read-only #设置为只读
- #log-bin=MySQL-bin #将二进制日志关闭
2. 启动 MySQL 服务器
- [root@localhost ~]# service mysqld restart
- Starting mysqld (via systemctl): [ OK ]
3. 关联主服务器
- MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.73.133',MASTER_USER='repluser',MASTER_PASSWORD='centos',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=515;
- Query OK, 0 rows affected (0.01 sec)
4. 查看从服务器状态
- MariaDB [(none)]> SHOW SLAVE STATUS\G;
- *************************** 1. row ***************************
- Slave_IO_State:
- Master_Host: 192.168.73.133
- Master_User: repluser
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: MySQL-bin.000001
- Read_Master_Log_Pos: 515
- Relay_Log_File: localhost-relay-bin.000001
- Relay_Log_Pos: 4
- Relay_Master_Log_File: MySQL-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: 515
- Relay_Log_Space: 256
- 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
- Master_SSL_Crl:
- Master_SSL_Crlpath:
- Using_Gtid: No
- Gtid_IO_Pos:
- Replicate_Do_Domain_Ids:
- Replicate_Ignore_Domain_Ids:
- Parallel_Mode: conservative
- SQL_Delay: 0
- SQL_Remaining_Delay: NULL
- Slave_SQL_Running_State:
- 1 row in set (0.00 sec)
- ERROR: No query specified
5. 启动线程
- MariaDB [(none)]> START SLAVE;
- Query OK, 0 rows affected (0.00 sec)
测试
在主服务器上导入一个 hellodb 数据库
[root@localhost ~]# MySQL <hellodb_innodb.sql
从服务器上查看是否同步成功
- MariaDB [(none)]> SHOW DATABASES;
- +--------------------+
- | Database |
- +--------------------+
- | hellodb | #已经有 hellodb 库
- | information_schema |
- | MySQL |
- | performance_schema |
- | test |
- +--------------------+
- 5 rows in set (0.00 sec)
主服务器已有数据的情况下搭建主从
拥有一台已经有数据的 MySQL 服务器, 追加一台从服务器
服务器 | IP 地址 |
---|---|
Master | 192.168.73.148 |
Slave | 192.168.73.149 |
Master 中的数据
- [root@localhost ~]# MySQL -e "SHOW DATABASES;"
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | hellodb |
- | MySQL |
- | performance_schema |
- | test |
- | test1 |
- +--------------------+
配置主服务器
1. 修改主服务器配置文件
- [root@localhost ~]# VIM /etc/my.cnf
- [mysqld]
- log-bin=/data/bin/MySQL-bin
- binlog-format=row
- [root@localhost ~]# systemctl restart mariadb
2. 创建复制账号
[root@localhost ~]# MySQL -e "GRANT REPLICATION SLAVE ON *.* TO'repluser'@'192.168.73.%'IDENTIFIED BY'centos';"
3. 备份主节点中的所有数据
- [root@localhost ~]# mysqldump -A --single-transaction -F --master-data=1> /data/all.sql
- [root@localhost ~]# VIM /data/all.sql
4. 将备份数据传送给从节点
[root@localhost ~]# scp /data/all.sql 192.168.73.149:/data
配置从服务器
1. 修改配置文件
- [root@localhost ~]# VIM /etc/my.cnf
- [mysqld]
- server-id=2
- read-only
2. 清空 MySQL 数据库
[root@localhost ~]# rm -rf /var/lib/MySQL/*
3. 修改备份数据将 chang master to 加入文件中
- CHANGE MASTER TO
- MASTER_HOST='192.168.73.148',
- MASTER_USER='repluser',
- MASTER_PASSWORD='centos',
- MASTER_PORT=3306,
- MASTER_LOG_FILE='mysql-bin.000005',
- MASTER_LOG_POS=245;
4. 启动 MySQL 服务, 并导入备份
[root@localhost ~]# MySQL </data/all.sql
5. 查看从节点状态
- MariaDB [(none)]> SHOW SLAVE STATUS\G;
- *************************** 1. row ***************************
- Slave_IO_State:
- Master_Host: 192.168.73.148
- Master_User: repluser
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: MySQL-bin.000005
- Read_Master_Log_Pos: 245
- Relay_Log_File: mariadb-relay-bin.000001
- Relay_Log_Pos: 4
- Relay_Master_Log_File: MySQL-bin.000005
- 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: 245
- 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)
6. 启动线程
- MariaDB [(none)]> START SLAVE;
- Query OK, 0 rows affected (0.00 sec)
7. 再次查看从节点状态, 复制的 2 个线程已经启动
- MariaDB [(none)]> SHOW SLAVE STATUS\G;
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: 192.168.73.148
- Master_User: repluser
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: MySQL-bin.000005
- Read_Master_Log_Pos: 245
- Relay_Log_File: mariadb-relay-bin.000002
- Relay_Log_Pos: 529
- Relay_Master_Log_File: MySQL-bin.000005
- 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: 245
- Relay_Log_Space: 825
- 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)
测试
主节点删 test1 库
- MariaDB [(none)]> DROP DATABASE test1;
- Query OK, 1 row affected (0.01 sec)
从节点查看是否同步
- MariaDB [(none)]> SHOW DATABASES;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | hellodb |
- | MySQL |
- | performance_schema |
- | test |
- +--------------------+
- 5 rows in set (0.00 sec) #已经没有 test1 库
更多详情见请继续阅读下一页的精彩内容: https://www.linuxidc.com/Linux/2019-05/158646p2.htm
来源: http://www.linuxidc.com/Linux/2019-05/158646.htm