Build a mysql master/slave server in ubuntu14 _ MySQL

Build the mysql master/slave server Ubuntu under ubuntu14

The master-slave replication of mysql is asynchronous. it is divided into master/slave. there is an IO thread on the master side, and there are IO and SQL threads under the slave.

The most important thing to set up the environment is to enable the mysql binary log function. I did not pay attention to the details during the setup process, and it took me a long time.

Environment:

My two virtual machines use the bridge mode to access the internet. nat is not recommended. first, set a fixed IP address for the two virtual machines in the same segment as the IP address of your physical machine. in this way, your virtual machine works like a local area network physical machine.

Set a fixed IP address:

$ Sudo vim/etc/network/interfaces

As follows:

# Interfaces (5) file used by ifup (8) and ifdown (8)

Auto lo

Iface lo inet loopback

Auto eth0

Iface eth0 inet static

Address 192.168.1.252

Netmask 255.255.255.0

Gateway 192.168.1.1

$ Sudo/etc/init. d/networking restart

My virtual machine addresses are set to 192.168.1.20.192.168.1.252 gateway IP address: 192.168.1.1

After the configuration is complete, ping each other in the virtual machine.

After the environment is ready, you can start. I didn't adopt the rpm method. it is very convenient to directly install mysql online in ubuntu, saving many steps.

$ Sudo apt-get install mysql-server

By default, it is automatically enabled after installation, so use netstat-tap | grep mysql to check whether the mysql service exists.

Then query the mysql running status, service mysql status. Similarly, there are other commands, service mysql start/stop/restart.

If the following information is displayed, the operation is successful.

Mysql start/running, process 1199

Comment out bind-address under/etc/mysql/my. cnf before login, because it can only be accessed locally by default.

Log on to mysql

Mysql-u root-p

Grant replication slave, reload, super on *. * to slave @ 192.168.1.152 identified by '20140901'

Then, in another Ubuntu remote test

Mysql-u slave-h 192.168.1.151-p

The preliminary work is ready. now you can configure it:

Modify my. cnf on the host of master 192.168.1.6.2. remember to add the following configuration information to the [mysqld] module. if you place it elsewhere in the file, the master configuration will fail.

Server-id = 1

Log_bin =/var/log/mysql/mysql-bin.log

Binlog_do_db = student

Binlog_ignore_db = mysql

Restart mysql

Sudo/etc/init. d/mysql restart

If failed occurs during the restart process, we recommend that you first check the location of the error log file corresponding to log-error in my. cnf, and then cat to check the error message.

Then enter mysql of the master. First, check whether the binary function is on.

Show variables like 'log %'

If it is off, it indicates that the function is not enabled. then, check whether the my. cnf file is in the middle of [mysqld. File path is correct

Show master status; the corresponding file position information is displayed, which will be used when the slave is set.

Finally, configure it in/etc/mysql/my. cnf of salve 192.168.1.252:

Server-id = 2

Log_bin =/var/log/mysql/mysql-bin.log

Replicate_do_db = student

Restart mysql

Sudo/etc/init. d/mysql restart

Log on to mysql with slave:

Stop slave

Then, set the log replication configuration from the master.

Change master to master_host = '192. 168.1.151 ', master_user = 'Slave', master_password = '2016 ',

Master_log_file = 'Log. 000004 ', master_log_pos = 94;

Log_file log_pos is the file position information displayed in show master status under the master.

Start slave;

Show slave status/G

In the displayed information, find Slave_IO_Running/Slave_ SQL _Running. If YES, the operation is successful.

If NO is displayed, you can view the log-error file, which is clearly described.