MySQL Master-slave replication

Master-slave replication provides better customer response times by slicing the load of client queries between the primary server and the server.

The advantages of MySQL master-slave replication are as follows:

• Increased robustness, a problem with the primary server, switching to a backup from the server.

• Optimize response times and do not update at the same time on the master and slave servers, which may cause conflicts

• The primary server continues to process updates during backup from the server

How master-slave replication works:

• The primary server saves the user's update operations to the database in binary format in a binary log log file, which is then transferred by the Binlog dump thread to the server for the Binarylog log file.

• Copy the update operation from the primary server binary log file to a relay log relay log file from the server through the I/O thread

• The operations in the relay log log file are executed locally from the server via a SQL thread to enable master-slave synchronization

• 
  

Configuration of master-slave replication

1. Ensure that the primary server version is not higher than the version from the server

2. Grant a connection account to the primary server

GRANT replication Slave on *.*to [email protected] '% ',//means to create user named root user, identified by ' 110 '; Connect the password from the server to the master server

3. Configure the primary server

Windows opens My.ini,linux user opens my.cnf

Log-bin = Mysql-binserver-id//My MySQL version is 5.5.20 is already configured by default

4, restart the primary server after default configuration

5, run

mysql> SHOW MASTER STATUS;

File indicates that the server is using the Binlog file (which is copied from the server), and the value of Position is the same as the file size of Binlog, indicating the location of the next logged event

Where binlog_do_db is which database is used by default, binlog_ignore_db is ignoring which database

6, configure the slave server

The server_id from the server is different from the primary server

Log-bin = mysql-binserver_id = 2log_slave_updates = 1//allows the repository to record its important events into its own binaries read_only//This option will block any unprivileged permission lines Cheng Change data

7, specify primary server information

Change Master to Master_host = ' 192.168.0.10 ',//IP address of primary server Master_user = ' root ', Master_password = ' on ', Master_log_                FILE = ' mysql-bin.000002 ', master_log_pos = 0; Start reading from the beginning of the log

8 to see if the settings are correct

SHOW SLAVE Status\g

Execute statement start slave

See the I/O threads and SQL threads for the server have started to run

We set up a database in the master server Test1

You can see that we've also seen test1 insert data from the server.

Error issues

When we exit from the MySQL command line from the server

Go to MySQL command line again error This is due to a problem with permissions

At this point we can go into the/var/lib/mysql/delete (cautious, deleted after the table will not be found back )

Sudo/etc/init.d/mysql Restart//restart MySQL server

It turns out it's still going to be an error

We went into the/var/log/mysql/error.log to see the error

We found out that our port 3306 is already occupied.

Netstat-anp|grep 3306//View the program that is using 3306 ports//Find his Pidsudo kill-9 PID//end-of-process Sudo/etc/init.d/mysql res Tart//Restart MySQL server su mysql//with Safe mode

MySQL Master-slave replication