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

Environment: 1. (main) linux centOS 7 64-bit

2. (from) linux centOS 7 64 bit

3. (mysql) it is best to require the version to be consistent, and the slave library cannot be higher than the master version.

Centos 7 installs mariadb by default, and installs mysql 5.7. refer to the following link documentation:

Centos 7 installs mysql 5.7: https://juejin.im/post/5c088b066fb9a049d4419985 (reproduced)

I. configuration of the main library

1. Change the main library / etc/my.cnf configuration file

Vim / etc/my.cnf

[mysqld]

Lower_case_table_names=1

Max_connections=7000

Group_concat_max_len = 202400

Max_allowed_packet = 128m

# enable gtid function

Gtid-mode=on

Enforce-gtid-consistency=1

# set server_id, which is generally set to IP, and be careful to be unique

Server_id=840

Replication filtering: that is, to specify which database is not synchronized (mysql libraries are generally out of sync)

Replicate_wild_ignore_table=mysql.%

# specify which database to synchronize, this time only synchronize the newerp library

Replicate_wild_do_table=newerp.%

# enable binary log function, and you can take it at will. It had better have meaning. This is the key point.

Log-bin=edu-mysql-bin

The memory allocated for each session, the cache used to store binary logs during a transaction

Binlog_cache_size=1M

Master-slave copy format mixed,statement,row. The default format is statement.

Binlog_format=mixed

The number of days that binary logs are automatically deleted / expired. The default value is 0, which means that it is not deleted automatically.

Expire_logs_days=7

Skip all errors or specified types of errors encountered in master-slave replication to avoid interruption of slave replication.

For example, the 1062 error refers to some duplicate primary keys, and the 1032 error is due to data inconsistency between the master and slave databases.

Slave_skip_errors=1062

Master_info_repository=TABLE

Relay_log_info_repository=TABLE

2. Restart the mysql database

Systemctl restart mysqld

3. Create a synchronization account and view master information

[tomcat@iZ2zeij9pa0qnzjt5wcr4kZ] $msyql-uroot-p

Enter password:

Mysql > GRANT REPLICATION SLAVE ON. To 'slave_account' @'% 'identified by' 123456'

Mysql > FLUSH PRIVILEGES

Mysql > SHOW MASTER STATUS\ G

1. Row

File: edu-mysql-bin.000031

Position: 1210791

Binlog_Do_DB: newerp

Binlog_Ignore_DB: mysql

Executed_Gtid_Set: 2abeaffc-6158-11e7-8222-00163e03196b:1-16151

1 row in set (0.00 sec)

ERROR:

No query specified

Mysql >

Note: 'slave_account' creates a synchronous user for the main library, and' 123456' is the database slave_account user password.

Note: record File: edu-mysql-bin.000031 and Position: 1210791, which will be used later from the library.

4. Mysqldump exports newerp library to slave library

Mysqldump-uroot-p123456-hlocalhost-- single-transaction-- master-data=2 newerp > / data/newerp_back.sql

Wrote a script for reference.

> DB_USER= "root" > DB_PASS= "123456!" > DB_HOST= "localhost" > DB_NAME= "newerp" > BIN_DIR= "/ usr/bin" > BCK_DIR= "/ data" > DATE=date +% Y-%m-%d_%H-%M-%S > $BIN_DIR/mysqldump-u$DB_USER-p$DB_PASS-h$DB_HOST-single-transaction-master-data=2-routines-flush-logs $DB_NAME > $BCK_DIR/$DB_NAME.$DATE.sql

5. Transfer the backup file scp to the slave library

Scp-P 22312 / data/newerp_back.sql root@ slave library IP:/root/

Password:

The configuration of the master library is complete and the configuration of the slave library begins.

Second, slave library configuration

1. Slave library / etc/my.cnf configuration

[mysqld]

Gtid-mode=on # features after enabling the gtid,5.6 version

Enforce-gtid-consistency=1

Server_id=3026 # id is required and should not be the same as the main library id

Replicate-ignore-db=mysql # means that the mysql library is out of sync, and you can write multiple

Replicate-do-db=newerp # means that only newerp libraries can be synchronized and multiple writes can be made.

2. Restart the mysqld service after the change

Systemctl restart mysqld

3. Import newerp_back.sql data

[root@localhost] # mysql-uroot-p

Change master to master_host='10.175.18.40',master_user='slave_account',master_password='123456',master_log_file='edu-mysql-bin.000031',master_log_pos=1210791

Mysql > start slave; # enables synchronization, stop slave stops synchronization, and reset master resets master database information

Mysql > `show slave status\ G

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 37

Current database: NONE

1. Row

Slave_IO_State: Waiting for master to send event

Master_Host: 10.175.18.40

Master_User: slave_account

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: edu-mysql-bin.000032

Read_Master_Log_Pos: 1031964

Relay_Log_File: localhost-relay-bin.000004

Relay_Log_Pos: 1032185

Relay_Master_Log_File: edu-mysql-bin.000032

Slave_IO_Running: Yes # both indicate that synchronization is enabled successfully for YES.

Slave_SQL_Running: Yes # both indicate that synchronization is enabled successfully for YES.

Replicate_Do_DB: newerp

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: 1031964

Relay_Log_Space: 1032606

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: 840

Master_UUID: 2abeaffc-6158-11e7-8222-00163e03196b

Master_Info_File: / var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 2abeaffc-6158-11e7-8222-00163e03196b:10626-16016

Executed_Gtid_Set: 2abeaffc-6158-11e7-8222-00163e03196b:1-16016

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

ERROR:

No query specified

Mysql >

Note: master_host='10.175.18.40' is the main library IP,master_user='slave_account' to create synchronous users, master_password='123456' is the database slave_account user password, master_log_file='edu-mysql-bin.000031',master_log_pos=1210791; two items are obtained from the main library. Execute the sql command from the main library: SHOW MASTER STATUS

5. Test

Create a new table or insert new data in the master library to see if the slave library is synchronized.

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.