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

MYSQL Master / Slave Library Construction (original)

I. configuration of a single main library

1.1 description of the environment

The environment described in this section is a master library and two slave libraries, as follows.

1.1.1Linux version

Red Hat Enterprise Linux Server release 6.4 (Santiago)

1.1.2 mysql version

5.1.73

1.1.3 master server ip 192.168.212.134, slaver server IP 192.168.212.142, slaver server IP 192.168.212.143

1.2 master server configuration

1.2.1 create a synchronization account

Mysql > grant replication slave on. To 'rep'@'192.168.212.%' identified by' rep'

Mysql > flush privileges

Description:. All tables that represent all databases, or you can specify that all tables in a database be synchronized, such as testdb.*

192.168.212.% means that the master database can be connected to the 192.168.212 network segment.

1.2.2 Open the binlog function of the master database

Edit / etc/my.cng

Add the bin-log parameter to the mysqld module. For example, bin-log=master

Restart the database

# service mysqld restart

1.2.3 back up the database

 checks the binlog for consistency.

Mysql > show master status

The current statement node is in mysqlbin.000002, with a location of 106. this is where the recovery starts in the slaver database.

Mysql > show master logs

 backup full library

In order to maintain data consistency, non-innodb database tables need to be locked for backup. You can use the mysql command mysql > flush table with read lock; to lock the database (unlock tables; unlocks). The time of table locking is controlled by the system parameters wait_time and interactive_timeout. The default time is 28800 seconds. If the execution window of this command is closed, the lock table is also invalidated.

You can also add the parameter-x to mysqldump to lock the table, as follows: # mysqldump-uroot-B-A-x-- events-master-data=2 | gzip > / root/mysql_all.gz

For innodb storage engine tables, you can add parameters to mysqldump-single-transaction snapshots during backup to keep data consistent, as follows: # mysqldump-uroot-B-A-single-transaction-- events-- master-data=2 | gzip > / root/mysql_all.gz

Transfer the backup file to the slaver server as follows:

Scp / root/mysql_all.gz root@192.168.212.141:/home/mysql/

1.3 Slaver server configuration

This section only configures 192.168.212.142, and the 192.168.212.143 configuration is the same.

1.3.1 Import the backup data into the database as follows:

$gzip-dv / root/mysql_all.gz

$mysql-uroot change master to

Master_host='192.168.212.134'

Master_port=3306

Master_user='rep'

Master_password='rep'

Master_log_file='mysqlbin.000002'

Master_log_pos=106

Description:

Fill in the master_log_file,master_log_pos value of the change master command in the backup file into the corresponding location of the above command. If you set-master-data=1 (2 will annotate the incremental location information) during the backup, the incremental location information will be written to the backup file, and the location information will be imported together when the backup file is imported from the database, so that you no longer need to write master_log_file and master_log_pos when performing change master from the library.

1.3.3 start synchronization

Mysql > start slave

1.3.4 check master-slave status

Mysql > show slave status\ G

Mysql > show slave status\ G

1. Row

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.212.134

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysqlbin.000004

Read_Master_Log_Pos: 106

Relay_Log_File: mysqld-relay-bin.000004

Relay_Log_Pos: 250

Relay_Master_Log_File: mysqlbin.000004

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

Relay_Log_Space: 550

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)

ERROR:

No query specified

Note: if both Slave_IO_Running and Slave_SQL_Running are yes, Seconds_Behind_Master indicates that the slave database lags behind the master database (in seconds).

II. Configuration of multiple main libraries

The multi-master library mode contains more than one main library, and the databases between the main libraries are synchronized with each other. Master1 and Master2 represent the two main libraries respectively.

2.1 Parameter configuration

Mater1:

Auto_increment_offset=1 # resolves the conflict of primary key increment variables. Self-increasing initial position, this example is 1

Auto_increment_increment=2 # resolves the conflict of primary key increment variables. The self-increasing interval is 2, for example, 1, 3, 5

Log_slave_updates

Mater2:

Auto_increment_offset=2 # resolves conflicts in primary key increments. Self-increasing initial position, this example is 2

Auto_increment_increment=2 # resolves conflicts in primary key increments. The self-increasing interval is 2, for example, 2, 4, 6

Log_slave_updates

2.2 back up the data of master1 and import it into mater2 database

Refer to the backup and import section of the single main library.

2.3 master1 initiates synchronization

Mysql > change master to

Master_host='192.168.212.141'

Master_port=3306

Master_user='rep'

Master_password='rep'

Mysql > start slave

2.4 master2 initiates synchronization

Mysql > change master to

Master_host='192.168.212.141'

Master_port=3306

Master_user='rep'

Master_password='rep'

Mysql > start slave

III. Automatic deletion of Binlog logs

You can configure the number of Binlog log retention days through the system parameter expire_logs_days. The default value of this parameter is 0

For example, expire_logs_days=10 means to keep it for 10 days.

IV. Separation of reading and writing

In order to prevent the application from DML the slave library and the parameters are inconsistent, you can add the read-only parameter to the slave library to make the slave library read-only (super, or all privileges permission users are not subject to this restriction).

Switch from the library to the new main library

5.1 Select the latest slave library as the new master library.

5.1.1 View each slave library if the red box below shows that the synchronization is consistent.

5.1.2 check the master.info files of the two slave libraries, and compare the differences to determine whether the red part of the slave library is up-to-date. Select the largest logbinfile and pos as the latest. This example is logbinfile:000006,pos:106.

# cat master.info

fifteen

Mysqlbin.000006

one hundred and six

192.168.212.134

Rep

Rep

3306

sixty

0

Import all the relaylog content from the library into the library.

Execute stop slave io_thread;show processlist\ G at each slave library; until you see that Has read all relay log; indicates that the import from the library is complete.

Mysql > stop slave io_thread

Mysql > show processlist\ G

5.3 New main library operation

Check of the new main library

 deletes two master.info,relay-log.info files under the data directory of the new host.

 confirms that the mysql.user table meets the connection requirements of the application and other slave libraries.

 confirms that the read-only and log_slave_updates system parameters are removed.

 confirms that the binlog feature is turned on.

 checks whether the original master library has binlog that is not synchronized to the new master library, and the binlog that is not synchronized to the new slave library generates a readable command document through the mysqlbinlog tool and imports it into the new master library.

Execute the following statement on the latest slave library to change the slave library to the new master library, as follows.

Mysql > stop slave;-- stop the slave service

Mysql > reset master;-initialize the main library binlog

# service mysqld restart restart the database

5.4 other slave operations

Mysql > stop slave

Mysql > change master to master host='192.168.212.142';-- point to the new main library

Mysql > start slave

Mysql > show slave status\ G

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.