Mysql master-slave replication configuration method
MySQL supports unidirectional and asynchronous replication. During the replication process, one server acts as the master server, and one or more other servers act as the slave server.. The master server writes updates to the binary log file and maintains an index of the log file to track log loops. When an slave server is connected to the master server, it notifies the master server of the last successful update location read from the server in the log. The slave server receives any updates from that time, blocks them, and waits for the master server to notify you of the next update.
Why do I use master-slave replication?
1. The master server/slave server settings increase robustness. When the master server encounters a problem, you can switch to the slave server as a backup.
2. By splitting the customer query load between the master server and slave server, a better customer response time can be obtained. But do not perform updates on the Master/Slave servers at the same time, which may cause conflicts.
3. Another advantage of using replication is that one slave server can be used for backup without interfering with the master server. During the backup process, the master server can continue to process updates.
MySQL uses three threads to execute the replication function (one of them is on the master server and the other two are on the slave server. When start slave is issued, an I/O thread is created from the server to connect to the master server and send binary logs to the master server. The master server creates a thread to send the binary log content to the slave server. Read the content sent by the Binlog Dump thread of the master server from the server I/O thread and copy the data to the local file in the data directory of the slave server, that is, the relay log. The first thread is an SQL thread. The server uses this thread to read relay logs and execute updates contained in the logs. The show processlist statement can be used to query information about replication on the master server and slave server.
The default relay log uses a file name in host_name-relay-bin.nnnnnn format, where host_name is the slave server host name and nnnnnnnn is the sequential column number. Create a continuous relay log file with a continuous serial number, starting from 000001. Track the relay log index file from the server to identify the currently used relay logs. The default relay log index file name is the host_name-relay-bin.index. By default, these files are created in the data directory of the slave server. Relay logs are in the same format as binary logs and can be read using mysqlbinlog. After the SQL thread executes all the events in the relay log, the relay log is automatically deleted.
The slave server creates two more state files --master.info and relay-log.info in the data directory. Status files are stored on the hard disk and will not be lost when the slave server is closed. When starting the next time from the server, read these files to determine how many binary logs it has read from the master server and how much it will process its own relay logs.
1. Configure the master server
Enable mysql Configuration
vim/usr/local/webserver/mysql/my.cnf
Enable server_id and bin-log
Server_id = 'unique value' (enable server_id)
Log-bin =/data/mysql/3306/binlog
2. Configure the slave server
Enable mysql Configuration
vim /usr/local/webserver/mysql/my.cnf
Enable server_id and bin-log
Server_id = 'unique value' (enable server_id)
log-bin = /data/mysql/3306/binlog/binlog
After modifying the configuration, remember to restart the Database service mysqld restart.
Key point: the slave database must have a user who can connect to the master server on the slave database machine.
3. In the primary database, assign a user to the slave Database
Enter the mysql/usr/local/webserver/mysql/bin/mysql-p Password
Mysql> grant all on *. * to user @ slave database ip address identified by 'Password ';
Flush privileges; refresh permission
View the show master status in the master database. The latest bin-log File Name of the master database.
4. Go to the slave database and execute the following SQL statement
Check whether the slave database can access
mysql /usr/local/webserver/mysql/bin/mysql -u
The user-p password-h ip address of the primary database given by the primary database. If you can access the database, exit
mysql /usr/local/webserver/mysql/bin/mysql -u
Password
In particular, the slave database stop slave must be disabled before the change is executed;
Mysql> change master to master_host = 'master database ip', master_user = 'master database authorized Username ', master_password = 'master database authorized password ', master_log_file = 'file name of the bin-log of the primary database', master_log_pos = 'position value of the bin-log of the primary database'
5. Enable the slave database start slave;
6. view the slave database status show slave status \ G;
change master to master_host='101.200.136.226',master_user='zt',master_password='zt',master_log_file=' binlog.000001 ',master_log_pos=4826;
If
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
OK
Master/Slave configuration complete
Binlog command
/usr/local/webserver/mysql/bin/mysqlbinlog binlog.000001
View binlog log files
Reset master binlog Initialization
Show master status; view the latest log files and position values
Flush logs refresh log files
Start slave enable slave Database
Stop slave Database
Show slave status; view slave database status
Restore data through binlog
Enter the binlog directory
cd /data/mysql/3306/binlog/
Viewing the binlog list ls
View binlog log files
/usr/local/webserver/mysql/bin/mysqlbinlog binlog.000001
Restore data
/Usr/local/webserver/mysql/bin/mysqlbinlog binlog.000001 |/usr/local/webserver/mysql/bin/mysql-p (restored before binlog is cleared)
If a reset master is executed, it cannot be restored.
The above is to sort out the configuration data for master-slave replication of the mysql database. If you need to develop a database, you can check it.