Synchronous replication of Master/Slave databases of MySQL5 on a Linux Server

1. Install MySQL ()
2. Start Multiple MySQL servers
To enable multiple MySQL servers on a single machine, you can use mysqld_safe to run multiple servers.
Of course, it is troublesome to edit the configuration file separately and read the configuration file when the server is shut down. Another method is to use
MySQL provides the tool mysqld_multi to manage multiple servers. The following method is mysqld_safe.
3. Prerequisites
Assume that MySQL is installed in the/usr/local/mysql/directory and is set to MYSQL_DIR, which is usually a link file.
The data file directory is $ MYSQL_DIR/data.
Now you need to add the data directory of another server. To simulate synchronization from a distributed server, it is impossible for multiple servers to share a data directory.
$ Cd $ MYSQL_DIR
$ Sudo cp-r-p data var2
The preceding command retains the original data to the permission and copies it to the var2 and var2 is the data directory of another server. In this way, in the original
The data of the two database servers is consistent.
4. Assume that the valid MySQL user root: root.
5. Start the server after installation
$ Cd $ MYSQL_DIR/bin
$ Sudo./mysqld_safe -- user = mysql -- binlog-do-db = test &
The above command indicates to start the server and use binary logs to record the update action of the database test.
6. test whether the startup is successful
$ Mysql-u root-p-S/tmp/mysql. sock
After you enter the password, if you can successfully log on, it indicates that the password is successful. The 2002 error is the most likely here, indicating that the socket file is incorrect. You can
Use commands
$ Ps aux | grep mysql
To view the socket file used by the current server, and then use the corresponding socket file when logging on.
7. Edit the configuration file
$ Sudo vi/etc/my. cnf
At first, this configuration file was for the server started earlier. Now we can modify it and then start another server.
Find the [mysqld] section and modify it as follows:
[Mysqld]
Server-id = 2 # originally 1
Socket =/tmp/mysql. sock2 # originally/tmp/mysql. sock
Port = 3307 # originally 3306
# The following three rows are added
Pid-file = $ MYSQL_DIR/var2/localhost. pid2
Datadir = $ MYSQL_DIR/var2
Log = $ MYSQL_DIR/var2/db2.log
Note that the preceding MySQL installation directory is used to replace the above $ MYSQL_DIR.
8. Start the second Server
$ Cd $ MYSQL_DIR/bin
$ Sudo./mysqld_safe -- user = mysql &
9. Test the second Server
$ Mysql-u root-p-P 3307-S/tmp/mysql. sock2
After you enter the password, you must be able to correctly connect to the mysql server.
Now, the two servers can run normally on the same machine, and the rest is to configure the master and slave servers, and then let the master server update and slave server
Connect to the master server and maintain synchronization.
10. synchronization server
Note that we use the binlog-do-db = test parameter when starting the first server. We want to update the database test.
Operations are recorded in binary log files.
1) log on to the master server
$ Mysql-u root-p-P 3306-S/tmp/mysql. sock
2) view the status of the master server
Mysql> show processlist \ G
After executing the preceding command, we should see at least two threads. The first is the login thread, and the second is to send binary logs.
.
Mysql> flush tables with read lock;
Mysql> show master status;
Mysql> unlock tables;
Remember the output result of the show master status \ G command. The File here is the binary log File, Position is the offset, Binlog_Do_DB
Indicates which database records are updated, and Binlog_Ignore_DB indicates which database updates are ignored. File and
Position.
3) log on to the slave server
$ Mysql-u root-p-P 3307-S/tmp/mysql. sock2
4) configure the slave server
First, make sure to stop the synchronization thread from the slave server.
Mysql> stop slave;
Then set the master server Parameters
Mysql> change master
-> Master_host = '2017. 0.0.1 ',
-> Master_user = 'root ',
-> Master_password = 'root ',
-> Master_log_file = 'mysql-bin.20.16 ',
-> Master_log_pos = 102;
Finally, start the synchronization thread from the server.
Mysql> start slave;
Check whether the synchronization thread on the slave server is started successfully
Mysql> show slave status \ G
If I/O and LOG threads are both YES from the above output, it indicates that the startup is successful, and the last line of output indicates that
The number of lags behind the master server.
View threads
Mysql> show processlist;
After the synchronization from the server is successfully started, the above command should output at least three threads, the first is the login thread, the second is the IO thread
Three are log threads.
11. Test Synchronization
Perform any update operations on the database test on the master server, and view the UPDATE results on the slave server immediately.