Mysql5.5 Master/Slave Configuration

Address: http://wubolu.iteye.com/blog/721131

In MySQL 5.5, the semi-synchronous replication mode (semi-synchronous replication) is introduced to successfully avoid the risk of data loss. In this mode, the master will wait until the BINLOG is successfully transferred and at least one slave's delay log is written. Otherwise, it will wait until timeout (10 s by default ). When a timeout occurs, the master automatically switches the semi-Sync Mode to asynchronous mode until at least one slave receives and sends acknowledge, and the master switches back to the semi-Sync Mode. With this new function, we can ensure the absolute security of synchronization data on the premise that the transaction throughput is allowed to be lost, because when you set timeout to a value large enough, any submitted data is securely sent to slave.

The semi-synchronous mode is actually implemented as a plugin of mysql5.5. The master and slave use different plugins. This plug-in is not installed by default. After the official MySQL Server RPM package is installed by default, the plug-in is found under/usr/lib (64)/MySQL/plugin. After installing the plug-in, you must manually set system parameters to enable the semi-synchronous replication mode.

Certificate ------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

Master Server:

 

VI my. CNF
[Mysqld]
Log-bin = new_name
Server-id = 1 # configure a unique ID number, ranging from 1 to 32.

# Set the name of the database for master-slave replication or not, and set it on slave.

BINLOG-do-DB = Database Name

BINLOG-ignore-DB = Database Name

 

Install plug-ins

 

Mysql> install plugin rpl_semi_sync_master soname 'semisync _ Master. so ';

The deletion plug-in is: uninstall plugin plugin_name;

Mysql> show status;

Rpl_semi_sync_master_clients 0 # indicates how many slave sets the semi-Sync Mode.

Rpl_semi_sync_master_no_tx 0 # indicates the number of submissions that failed to receive the slave receipt

Rpl_semi_sync_master_status off # indicates whether the current mode is asynchronous or semi-synchronous and is set to on. Set global rpl_semi_sync_master_enabled = 1;

Mysql>
Set global rpl_semi_sync_master_timeout = 1000; (1 S, default value: 10 s)

 

Mysql> grant replication slave on *. * To new_name @ slave server IP identified '**';

Mysql> flush tables with read lock;
Mysql> show Master Status; # Write down the current log file location.
Show: saturn-bin.002 106

 

 

Slave Server:

 

VI my. CNF
[Mysqld]
Server-id = 2 # is also unique.

# Set the name of the database for master-slave replication or not, and set it on the master.

Replicate-do-DB = Database Name

Replicate-ignore-DB = Database Name

 

Install plug-ins

Mysql> install plugin rpl_semi_sync_slave soname 'semisync _ slave. so ';
Mysql> change master to master_host = 'master server ip', master_user = 'authorized user (cannot be root) ', master_password = '', master_log_file = 'saturn-bin.002 ', master_log_pos = 106;

 

Set rpl_semi_sync_slave_enabled = on to restart mysql> slave stop; slave start

After checking that the settings are correct, the master server: mysql> unlock tables;

 

Mysql> show Master Status \ G # view Master Status

Mysql> show slave status \ G # view slave status

 

Certificate ------------------------------------------------------------------------------------------------------------------------------------------------------------------

Problem handling records:

1. The slave server goes down, causing an error after the show slave status \ G is started on the slave server.

Last_io_error: Got fatal error 1236 from master when reading data from binary log: 'binlog truncated In The Middle Of event'

Solution:

MASTER: show Master Status \ G # record the following information of the master

File: mysql-MasterSlave.000006
Position: 174367

SLAVE: slave stop;

Reset slave;

Change master to master_host = '192. 168.1.126 ', master_user = 'user', master_password = 'PW', master_log_file = 'mysql-masterslave.000006 ', master_log_pos = 192;

Tip: Starting from MySQL 5.5, MySQL has been added to semi-synchronous replication, and usage such as load data from Master has been canceled. After the MySQL slave/Master instance recovers from an exception, mySQL slave can process unfinished or discard master write data such as log failure, and then obtain the source data from the master again. Similarly, the restart of the master instance will not cause slave to lose data.

 

Certificate ------------------------------------------------------------------------------------------------------------------------------------------------------------------

Master synchronization thread status

The following lists the most common statuses in the BINLOG dump thread state field of the master. If there is no BINLOG dump thread on the master, the synchronization is not running. That is to say, there is no slave connection.
Sending BINLOG event to slave
An event is composed of binary logs. An event is usually composed of an update statement and other information. The thread reads an event and sends it to slave.
Finished reading one BINLOG; switching to next BINLOG
After reading a binary log, switch to the next one.
Has sent all BINLOG to slave; waiting for BINLOG to be updated
You have read all the incomplete update logs and sent them to slave. It is idle and is waiting to execute new update operations on the master to generate new events in binary logs and then read them.
Waiting to finalize termination
The current thread has stopped. This time is short.

Slave thread status
The following lists the most common states in the slave I/O thread state field. Starting from MySQL 4.1.1, The slave_io_state field in the result of executing the show slave status statement also appears. This means you can only execute the show slave status statement to learn more.
Connecting to master
This thread certificate tries to connect to the master.
Checking master version
Determine a transient status after the master node is connected.
Registering slave on Master
Determine a transient status after the master node is connected.
Requesting BINLOG dump
Determine a transient status after the master node is connected. This thread sends a request to the master to inform it of the binary file to be requested and the starting position.
Waiting to reconnect after a failed BINLOG Dump Request
If a binary log Dump Request fails (due to disconnection), the thread enters this state during sleep and reconnects regularly. The reconnection interval is specified by the -- master-connect-retry option.
Reconnecting after a failed BINLOG Dump Request
This thread is trying to reconnect to the master.
Waiting for Master to send event
Connected to the master, waiting for it to send binary logs. If the master is idle, the status may last for a long time. If it waits for more than slave_read_timeout seconds, a timeout occurs. In this case, it will consider disconnecting and then trying to reconnect.
Queueing master event to the relay log
An event has been read and is being copied to the relay log for processing by the SQL thread.
Waiting to reconnect after a failed master event read
An error occurred while reading the log (due to disconnection ). This thread sleep for Master-connect-retry seconds before re-connection.
Reconnecting after a failed master event read
Attempting to reconnect to the master. After the connection is confirmed, the status changes to waiting for Master to send event.
Waiting for the slave SQL thread to free enough relay log Space
The relay_log_space_limit value is non-zero. The total size of the relay log exceeds this value. The I/o thread waits for the SQL thread to process the relay logs and then delete them to free up enough space.
Waiting for slave mutex on exit
The current thread has stopped. This time is short.
Reading event from the relay log
Read an event from the relay log for execution.
Has read all relay log; waiting for the slave I/O thread to update it
All the events in the relay log have been processed and are waiting for the I/O thread to write the updated log.
Waiting for slave mutex on exit
The current thread has stopped. This time is short.

 

Certificate ------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

Appendix: last_ SQL _errno error code description:

 

1005: An error occurred while creating the table.
1006: database creation failed
1007: the database already exists. An error occurred while creating the database.
1008: the database does not exist. An error occurred while deleting the database.
1009: the database file cannot be deleted, leading to database deletion failure.
1010: failed to delete database because the data directory cannot be deleted
1011: An error occurred while deleting the database file.
1012: records in the system table cannot be read.
1020: the record has been modified by another user
1021: The remaining space on the hard disk is insufficient. please increase the available space on the hard disk.
1022: duplicate keywords. failed to change the record
1023: An error occurred while disabling the service.
1024: An error occurred while reading the file.
1025: An error occurred while changing the name
1026: file write error
1032: the record does not exist
1036: The data table is read-only and cannot be modified.
1037: The system memory is insufficient. Restart the database or restart the server.
1038: the memory used for sorting is insufficient. Increase the sorting buffer.
1040: Maximum number of connections to the database. Increase the number of available connections to the database.
1041: insufficient system memory
1042: Invalid Host Name
1043: Invalid connection
1044: the current user is not authorized to access the database
1045: the database cannot be connected. The user name or password is incorrect.
1048: The field cannot be blank
1049: the database does not exist.
1050: The data table already exists.
1051: The data table does not exist.
1054: The field does not exist.
1065: Invalid SQL statement. The SQL statement is empty.
1081: cannot establish socket connection
1114: The data table is full and cannot accommodate any records
1116: too many open data tables
1129: Database exception. Restart the database.
1130: failed to connect to the database. You are not authorized to connect to the database.
1133: the database user does not exist
1141: the current user is not authorized to access the database
1142: the current user is not authorized to access the data table
1143: the current user is not authorized to access fields in the data table.
1146: The data table does not exist.
1147: the user's access permission to the data table is not defined.
1149: SQL statement syntax error
1158: network error. Read error. Check the network connection status.
1159: network error. Read timeout. Check the network connection status.
1160: network error. A write error occurs. Check the network connection status.
1161: network error. Write timeout. Check the network connection status.
1062: The field value is repeated and the Database Import fails.
1169: The field value is repeated and the update record fails.
1177: An error occurred while opening the data table.
1180: Transaction submission failed
1181: Transaction rollback failed
1203: the maximum number of connections established between the current user and the database to reach the database. Increase the number of available database connections or restart the database.
1205: lock timeout
1211: the current user does not have the permission to create a user
1216: failed to check foreign key constraints and failed to update sub-table records
1217: failed to check foreign key constraints, failed to delete or modify the primary Table Record
1226: The resource used by the current user has exceeded the allowed resource. Please restart the database or the server.
1227: You are not authorized to perform this operation.
1235: mysql version is too low and does not have this function