服务器搭建mysql主从复制与读写分离的方法

Elinkcloud · 发表于 2021-11-23 11:00:54

mysql>flush privileges;server-id = 1 #主机标示，整数 log_bin = /var/log/mysql/mysql-bin.log #确保此文件可写，开启bin-log read-only =0 #主机，读写都可以 binlog-do-db =test #需要备份数据，多个写多行 binlog-ignore-db =mysql #不需要备份的数据库，多个写多行 mysql> flush tables with read lock;Query OK, 0 rows affected (0.19 sec)mysql> show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000007 | 517 | test | mysql |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)mysql> unlock tables;Query OK, 0 rows affected (0.28 sec)server-id = 2 log_bin = /var/log/mysql/mysql-bin.log master-host =192.168.10.111 master-user =backup master-pass =123456 master-port =3306 master-connect-retry=60 #如果从服务器发现主服务器断掉，重新连接的时间差(秒) replicate-do-db =test #只复制某个库 replicate-ignore-db=mysql #不复制某个库 mysql> start slave;mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.111 Master_User: root Master_Port: 3306 Connect_Retry: 5 Master_Log_File: mysql-bin.000007 Read_Master_Log_Pos: 263 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 408 Relay_Master_Log_File: mysql-bin.000007 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: test Replicate_Ignore_DB: mysql 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: 263 Relay_Log_Space: 564 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: 1 row in set (0.00 sec) [xjp@server22 ~]$mysql -uroot -p123456 mysql> create database test; mysql> create table user(id int); mysql> insert into user values(1),(2),(3),(4),(5),(6); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from user; +----+ | id | +----+ | 1 | | 2 | +----+ 2rows in set (0.00 sec) mysql> select * from user; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | +----+ 6 rows in set (0.00 sec) mysql> update user set id=11 where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from user; +----+ | id | +----+ | 11 | | 2 | | 3 | | 4 | | 5 | | 6 | +----+ 6 rows in set (0.00 sec) mysql> delete from user where id=2; Query OK, 1 row affected (0.00 sec) mysql> select * from user; +----+ | id | +----+ | 11 | | 3 | | 4 | | 5 | | 6 | +----+ 5 rows in set (0.00 sec) mysql> alter table user add name varchar(50); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from user; +----+------+ | id | name | +----+------+ | 11 | NULL | | 3 | NULL | | 4 | NULL | | 5 | NULL | | 6 | NULL | +----+------+ 5 rows in set (0.00 sec) mysql> ALTER TABLE user MODIFY COLUMN name VARCHAR(200); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> desc user; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(200) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> create table user2(id int); Query OK, 0 rows affected (0.01 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | test | | user | | user2 | +----------------+ 3 rows in set (0.00 sec) mysql> mysql> use test; Database changed mysql> select * from user; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | +----+ 2 rows in set (0.00 sec) mysql> select * from user; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | +----+ 6 rows in set (0.00 sec) mysql> select * from user; +----+ | id | +----+ | 11 | | 2 | | 3 | | 4 | | 5 | | 6 | +----+ 6 rows in set (0.00 sec) mysql> select * from user; +----+ | id | +----+ | 11 | | 3 | | 4 | | 5 | | 6 | +----+ 5 rows in set (0.00 sec) mysql> select * from user; +----+------+ | id | name | +----+------+ | 11 | NULL | | 3 | NULL | | 4 | NULL | | 5 | NULL | | 6 | NULL | +----+------+ 5 rows in set (0.00 sec) mysql> desc user; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(200) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | test | | user | | user2 | +----------------+ 3 rows in set (0.00 sec) mysql> mysql binlog日志查看： show binlog events\G; *************************** 12. row *************************** Log_name: mysql-bin.000007 Pos: 985 Event_type: Query Server_id: 1 End_log_pos: 1075 Info: use `test`; delete from user where id=2 *************************** 13. row *************************** Log_name: mysql-bin.000007 Pos: 1075 Event_type: Query Server_id: 1 End_log_pos: 1175 Info: use `test`; alter table user add name varchar(50) *************************** 14. row *************************** Log_name: mysql-bin.000007 Pos: 1175 Event_type: Query Server_id: 1 End_log_pos: 1287 Info: use `test`; ALTER TABLE user MODIFY COLUMN name VARCHAR(200) *************************** 15. row *************************** Log_name: mysql-bin.000007 Pos: 1287 Event_type: Query Server_id: 1 End_log_pos: 1376 Info: use `test`; create table user2(id int) 15 rows in set (0.00 sec) 很早的时候搭建的了，走不少弯路，根据当时记录的写了下
希望本文所述对大家CentOS服务器设置有所帮助。

对于总部网络，因为是三层结构，所以很容易部署防火墙和MPLS VPN.对访问各个分支机构的流量转发至MPLS VPN的CE端路由器。通过防火墙的UTM功能实现Trust区域到Untrust等指定区域之间的病毒过滤和IPS过滤。