Shulou(Shulou.com)06/01 Report--

Basic strategy:

At this point, you need to make a full backup on the master server and restore to the slave server; then turn on master-slave replication; if master-slave replication is used directly, then the master-slave server is under great pressure

Full backup of master server data:

[root@master] $mysqldump-A-F-- single-transaction-- master-data=1 > all.sql

The master server simulates the modification operation:

MariaDB [(none)] > create database wangdb1;Query OK, 1 row affected (0.01sec) MariaDB [(none)] > show master logs +-+-+ | Log_name | File_size | +-+-+ | mariadb-bin.000001 | 8217 | mariadb-bin.000002 | 555 | | mariadb-bin.000003 | 334 | +- -+-+ 3 rows in set (0.00 sec) [root@master ~] $less all.sqlCHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000003' MASTER_LOG_POS=245

Master server configuration:

[root@slave ~] $vim / etc/my.cnf [mysqld] datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# Settings user and group are ignored when systemd is used.innodb_file_per_tableserver_id=2

Restore from server data:

[root@master ~] $scp all.sql 192.168.27.17 root@slave ~] $mysql

CHANGE MASTER TO-> MASTER_HOST='192.168.27.7',-> MASTER_USER='repluser',-> MASTER_PASSWORD='centos',-> MASTER_LOG_FILE='mariadb-bin.000003',-> MASTER_LOG_POS=245;Query OK, 0 rows affected (0.01 sec) MariaDB [(none)] > start slave Query OK 0 rows affected (0.00 sec) MariaDB [(none)] > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.27.7 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000003 Read_Master_Log_Pos: 334 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 620 Relay_Master_Log_File: mariadb-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: 334 Relay_Log_Space: 916 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: 0Master_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: 11 row in set (0.00 sec)

Data consistency assurance from the server:

Since the data from the server can also be deleted, the read-only attribute can be turned on in order to ensure the consistency of the data. In this case, root users of MySQL can still modify and delete, but it is effective for ordinary users. MariaDB [(none)] > show variables like 'read_only' +-+-+ | Variable_name | Value | +-+-+ | read_only | OFF | +-+-+ 1 row in set (0.00 sec) [root@slave ~] $vim / etc/my .cnf [mysqld] datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# Settings user and group are ignored when systemd is used.innodb_file_per_tableserver_id=2read_onlyMariaDB [(none)] > grant select Update,delete on *. * to test@'192.168.27.%' identified by 'centos' Query OK, 0 rows affected (0.00 sec) [root@master ~] $mysql-utest-pcentos-h292.168.27.17MariaDB [hellodb] > select * from students where age='22' +-+ | StuID | Name | Age | Gender | ClassID | TeacherID | + -+-+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | 21 | Huang Yueying | 22 | F | 6 | NULL | +-+ -+ 3 rows in set (0.00 sec) MariaDB [hellodb] > delete from students ERROR 1290 (HY000): The MariaDB server is running with the-- read-only option so it cannot execute this statement

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.