Ubuntu16.04 implements master-slave MySQL replication and ubuntu16.04mysql

Ubuntu16.04 implements master-slave MySQL replication and ubuntu16.04mysql

Because there were not many business needs in the past two days, I took the time to study the master-slave synchronization of MySQL yesterday.

During this period, I encountered many problems. I was afraid I would forget it later. I also recorded my own growth experiences. So I wrote this blog post.

Because I'm testing on a vmvm.

Therefore, you must first change the connection mode of the virtual machine to the Bridge Mode.

Choose Settings> hardware> network adapter> network connection to change to bridging mode.

If you do not use the root user to log on, we recommend that you switch to the Root user to install sudo.

Set the IP address to a static IP address and enter it in the terminal.

vi /etc/network/interfaces

Modify the file and add the following code.

#interfaces(5) file used by ifup(8) and ifdown(8)auto loiface lo inet loopbackauto ens33iface ens33 inet staticaddress 192.168.6.119netmask 255.255.255.0gateway 192.168.6.2#dns-nameserver 119.29.29.29

Ens33 is queried using the ifconfig command, and may vary with each other. Some may be eth0 or others. Do not make a mistake. Otherwise, the network will not be connected.

Perform the preceding operations on the master and slave servers respectively. The IP address of the master server is 192.168.6.119 and the IP address of the master server is 192.168.6.200.

Ubuntu can also set static IP addresses through GUI settings.

Restart the NICS of the master and slave servers respectively.

Check whether the ping operation can ping each other.

 

Download and install MySQL in sequence. Online installation is very convenient.

apt-get install mysql-serverapt-get isntall mysql-client

 

Enter

vi /etc/mysql/mysql.conf.d/mysqld.cnf

Open the configuration file and comment out bind-address in the [mysqld] module to allow remote access to the database.

Add the following code to the [mysqld] module of the master server.

Server-id = 1 # server-id server Unique Identifier log_bin = master-bin # log_bin start MySQL binary log log_bin_index = master-bin.indexbinlog_do_db = myslave # binlog_do_db specify database binlog_ignore_db = mysql # binlog_ignore_db specifies a database that does not record binary logs.

Add the following code to the database configuration file of the server:

server-id = 2replicate-do-db =myslaverelay-log = slave-relay-binrelay-log-index = slave-relay-bin.index

 

Next, in the MySQL command line, change the account and permissions used to log on to the master server MySQL to create the slave server.

grant replication slave,reload,super on *.* to slave @192.168.6.200 identified by 'mypassword'

Restart the master-slave MySQL service.

service mysql restart

 

Log on to the MySQL database of the master server and view the status of the master database.

show master status;

Remember the values of the queried file_set and Position fields, which will be used later.

 

Enter the following code in the database command line of the Slave server to log on to the Slave server of Slave and connect to the Master server.

change master to master_host='192.168.6.199',master_port=3306,master_user='mydatabase',master_password='mypassword',master_log_file='master-bin.000003',master_log_pos=1698;

(Master_host corresponds to the master server IP address, master_port corresponds to the master server port, master_log_file corresponds to show master status display File column: master-bin.000003, master_log_pos corresponding Position column: 1698, otherwise, synchronization may fail .)

 

Start data synchronization when logging on to the slave server MySQL

start slave;

Log on to the slave server MySQL to view the synchronization status.

show slave status\G;

If both of the preceding values are Yes, the synchronization is successful. If Slave_IO_Running is ing, it may be used to log on to the Slave server, and the password or user name entered in the Master server is incorrect,

Or the network connection fails. Check whether the Virtual Machine IP address is the IP address you set.

 

Now, any insert, delete, and update operations on the Master database will be promptly updated to the Slave database.

Stop the synchronization command on the slave Database

stop slave;