Centos 7 system mysql master data synchronization, centosmysql

Centos 7 system mysql master data synchronization, centosmysql

Preparations

Master Server IP address 192.168.2.225

Slave Server IP address 192.168.20.8

Mysql master server

1. configure the firewall to allow 3306/tcp ports

[Root @ localhost ~] # Firewall-cmd -- zone = public -- add-port = 3306/tcp -- permanent

2. Disable selinux # selinux is secure in Linux

[Root @ localhost ~] # Setenforce 0
[Root @ localhost ~] # Getenforce
Disabled
3. Modify the/etc/my. cnf configuration file.

[Root @ localhost ~] # Vim/etc/my. cnf

Server-id = 1 # server ID, which must be unique in log-bin = mysql_bin # enable the binary log function, it is recommended that binlog_cache_size = 1 M # allocate memory for each session, which is used to store binary cache binlog_format = mixed # format of master-slave replication (mixed, statement, row, the default format is statement) expire_logs_days = 7 # Number of days when binary logs are automatically deleted/expired. The default value is 0, indicating that it is not automatically deleted. Slave_skip_errorrs = 1062# Skip all errors encountered in master-slave replication or errors of the specified type to avoid slave Terminal Services. For example, the error 1062 indicates some primary keys.

Repeated. The error 1032 is because the data in the master and slave databases is inconsistent.

Log_slave_updates = 1 log_slave_update indicates that slave writes the replication event to its own binary log.

Relay-log-index = slave-relay-bin.index # serves as a relay log for the slave server
Auto-increment = 2 # auto-incrementing factor (2 each time)
Auto-increment-offset = 1 # auto-increment offset (starting from 1), singular

[Root @ localhost ~] # Systemctl restart mysqld

4. Modify the mysql logon Password

[Root @ localhost ~] # Mysqladmin-u root password '123'
You have new mail in/var/spool/mail/root
[Root @ localhost ~] # Mysql-u root-p # log on to mysql

5. Authorize an account to the slave server

Mysql> grant replication slave on *. * to 'slave '@ '2017. 192. %' identified by '20160301'; # authorize an account to the slave server

Mysql> flush privileges; # refresh logs
Mysql> show master status; # view the log name in the File column and the offset in the position column. These two values are required when the slave server is configured later.

+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql_bin.000002 | 507 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)

6. Configure Synchronization

Mysql> change master to master_host = '2017. 168.20.8 ', master_user = 'slave', master_password = '000000', master_log_file = 'mysql _ bin.000007 ', master_log_pos = 123456; # modify the master_log_file and master_log_pos parameters in the preceding command according

Mysql> start slave; # start the slave Service

Mysql> show slave status \ G; # Check the Slave status and make sure the following two values are YES.

Slave_IO_Running: Yes
Slave_ SQL _Running: Yes

7. Verify data synchronization between the master and slave servers (including synchronizing 1 Database, 2 tables, 3 Records: records indicate data in the table)
Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mysql |
| Performance_schema |
| Test |
+ -------------------- +
4 rows in set (0.03 sec)

Mysql> create database lxy;
Query OK, 1 row affected (0.00 sec)

Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Lxy |
| Mysql |
| Performance_schema |
| Test |
+ -------------------- +
5 rows in set (0.00 sec)

Mysql> use lxy;

Mysql> create table user (id int (10) not null, name char (20) default '', primary key (id ));
Query OK, 0 rows affected (0.04 sec)

Mysql> show tables;
+ --------------- +
| Tables_in_lxy |
+ --------------- +
| User |
+ --------------- +
1 row in set (0.01 sec)
Mysql> insert into user values (1, 'Wo shi zhu Server ');
Query OK, 1 row affected (0.01 sec)

Mysql> select * from user;
+ ---- + ------------------- +
| Id | name |
+ ---- + ------------------- +
| 1 | wo shi zhu server |
+ ---- + ------------------- +
1 row in set (0.00 sec)

Mysql> select * from user;
+ ---- + -------------------- +
| Id | name |
+ ---- + -------------------- +
| 1 | wo shi zhu server |
| 2 | wo shi cong server |
+ ---- + -------------------- +
2 rows in set (0.00 sec)

Mysql slave server

1. configure the firewall to allow 3306/tcp ports

[Root @ localhost ~] # Firewall-cmd -- zone = public -- add-port = 3306/tcp -- permanent

2. Disable selinux # selinux is secure in Linux

[Root @ localhost ~] # Setenforce 0
[Root @ localhost ~] # Getenforce
Disabled

3. Modify the/etc/my. cnf configuration file.

[Root @ localhost ~] # Vim/etc/my. cnf

server-id       = 2relay-log = relay-binrelay-log-index = slave-relay-bin.index

Binlog_format = mixed

Binlog_cache_size = 1Mexpire_logs_days = 7slave_skip_errors = 1062log_slave_updates = 1auto_increment_increment = 2 # ID auto-increment starts from 2, double numberauto_increment_offset=2

4. Modify the mysql logon Password

[Root @ localhost ~] # Mysqladmin-u root-p password '000000'
Enter password:
[Root @ localhost ~] # Mysql-u root-p # log on to mysql

5. Configure Synchronization

Mysql> change master to master_host = '2017. 168.2.225 ', master_user = 'slave', master_password = '000000', master_log_file = 'mysql _ bin.000002 ', master_log_pos = 123456; # modify the master_log_file and master_log_pos parameters in the preceding command according

Mysql> start slave; # start the slave Service

Mysql> show slave status \ G; # Check the Slave status and make sure the following two values are YES.

Slave_IO_Running: Yes
Slave_ SQL _Running: Yes

6. Authorize the primary server account

Mysql> grant replication slave on *. * to 'slave '@ '2017. 192. %' identified by '20160301'; # authorize the primary server account

Mysql> flush privileges; # refresh logs
Mysql> show master status; # view the log name in the File column and the offset in the position column. These two values are required when configuring the master server.
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql_bin.000007 | 585 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)

7. Verify data synchronization between the master and slave servers (including synchronizing 1 Database, 2 tables, 3 Records: records indicate data in the table)

Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mysql |
| Performance_schema |
| Test |
+ -------------------- +
4 rows in set (0.03 sec)

Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Lxy |
| Mysql |
| Performance_schema |
| Test |
+ -------------------- +
5 rows in set (0.00 sec)

Mysql> use lxy;
Database changed
Mysql> show tables;
+ --------------- +
| Tables_in_lxy |
+ --------------- +
| User |
+ --------------- +
1 row in set (0.00 sec)

Mysql> select * from user;
+ ---- + ------------------- +
| Id | name |
+ ---- + ------------------- +
| 1 | wo shi zhu server |
+ ---- + ------------------- +
1 row in set (0.00 sec)

Mysql> insert into user values (2, 'Wo shi cong Server ');
Query OK, 1 row affected (0.01 sec)

Mysql> select * from user;
+ ---- + -------------------- +
| Id | name |
+ ---- + -------------------- +
| 1 | wo shi zhu server |
| 2 | wo shi cong server |
+ ---- + -------------------- +
2 rows in set (0.00 sec)