MySQL5.6 implementation of Master-slave replication, read/write separation, decentralized single server pressure

Nothing to do, in the local build several virtual machines, prepared with a MySQL read and write separate master and slave configuration, version selection of the latest version, mysql.5.6.28 version, the Department of the use of source installation (I always like the source installation, because I do not know how to install the CentOS installation directory, Also how to specify a variety of configurations, but some rely on the library and the like I do not directly use, the use of the source to install the installation directory, and sometimes a variety of mismatches are drunk)

Installation Environment: Centos 6.4

MySQL version: Master and slave use MySQL5.6.28

Machine situation: Use three, respectively for Centos1/centos3/centos4 (why not centos2 it, because Centos2 was I broke, no longer open), where Centos4 as the main library, the other two as from the library, of course, the hostname are named after Centos1/centos3/centos4, IP is 192.168.138.4 192.168.138.3 192.168.138.1

Use Ifconfig to view my configuration

Careful reunion found that I have two network card, which is mainly related to my company's environment, because the network management IP and MAC address binding, so my computer can only use fixed IP, virtual machine cannot get an IP, can not access the Internet. So I added a network card, one for the intranet using Host-only to specify a fixed IP, a way to use NAT shared host IP for the Internet.

This configuration is also not very complex, under the directory '/etc/sysconfig/network-scripts/' has a ' ifcfg-eth0 ' network card configuration, and then execute (note to HWADDR and your virtual machine's actual MAC address corresponding)

CP Ifcfg-eth0 ifcfg-eth1

Assign a copy of the configuration file, and then ' Ifcfg-eth0 ' is configured as follows

This NIC corresponds to a network card connected using the Host-only method, using a static IP

The configuration of ' ifcfg-eth1 ' is as follows

This network card corresponds to a network card using NAT, which is used to connect the external network and obtain IP dynamically.

After two, restart the network

Service Network restart

Note that if it doesn't work, see if you already have two NICs on your machine

No, add, and then view it on your virtual machine ' Edit '

The two networks belong to both 192.168.15.0 and 192.168.77.0, so your local VMnet8 is going to be a gateway, and you want to keep it in a network to communicate with the virtual machine and NAT configuration, so the VMNET8 configuration is as follows

This ensures that the virtual machine is able to surf the internet (the rest of the machines use the same steps to configure the network)

Well, the above is a digression, but the author took a long time to good grasp, I believe many students will encounter the above problems, both fixed IP, but also the Internet, but the company has made restrictions

To install MySQL first

Unzip the file first

tar -zxvf mysql-5.6. . tar. gz

But this thing has a few dependencies before the installation, ' Make gcc-c++ cmake bison-devel ncurses-devel ' here with Yum installation

Yum Install  Make gcc-c++ cmake bison-devel  ncurses-devel

Everything is ready, into the extracted source code installation package, here you will find it very surprising, there is no ' configure ' file, do not build, this is because the new version of the use of ' cmake ' for compiling, so make sure to install the CMake

Rpm-q CMake

If it is not installed, install it, and then enter the following instructions to configure it.

CMake-dcmake_install_prefix=/usr/local/MySQL-dmysql_datadir=/usr/local/mysql/Data-dsysconfdir=/etc-dwith_myisam_storage_engine=1 -dwith_innobase_storage_engine=1 -dwith_memory_storage_engine=1 -dwith_readline=1 -dmysql_unix_addr=/var/lib/mysql/Mysql.sock-dmysql_tcp_port=3306 -denabled_local_infile=1 -dwith_partition_storage_engine=1 -dextra_charsets= All-ddefault_charset=UTF8-ddefault_collation=utf8_general_ci

A few important places above

'-dcmake_install_prefix ' is the installation directory

'-dmysql_datadir ' is the data file storage directory

'-dsysconfdir ' configuration file directory

'-dmysql_unix_addr ' sock file storage directory

'-dmysql_tcp_port ' port number

No problem and then compile

 Make  Make Install

After 30 minutes long wait ...

When everything is ready, we need to make sure that the MySQL group and MySQL users are there

Cat /etc/group| grep ' MySQL ' cat /etc/passwd| grep ' MySQL '

If it does not exist, you need to add the combined user

-G MySQL MySQL

Then change the owner of the directory '/usr/local/mysql ' to MySQL

chown -R mysql:mysql/usr/local/mysql

This is already installed, but there is no data in MySQL (even if the system comes with no data), you need to execute the self-contained script, initialize the MySQL data

/usr/local/mysql/scripts/--basedir=/usr/local/mysql--datadir=/usr/local/mysql/data--user=mysql

After the execution, you will find some files in the/usr/local/mysql/data directory, these are the MySQL data files.

Then start MySQL, it is important to note that we specify the configuration file in the '/etc ' directory, but we want to put the MySQL configuration file to '/usr/local/mysql/' for convenience, then we need to change the name '/etc/my.conf '

mv /etc/my.cnf  /etc/my.cnf.bak

At this time, MySQL in the '/etc ' directory can not find, will go to ' $basedir ' go down to find, that is '/usr/local/mysql/' directory

At this point, the installation of MySQL is finished, the following will be launched, you will find the use of

/usr/local/mysql/bin/mysql start

Unable to start, error is

ERROR 2002 (HY000): Can ' t connect to local MySQL server through socket '/var/lib/mysql/mysql.sock ' (2)

Before the author how to do not, even if the establishment of '/var/lib/mysql/mysql.sock ' later finally know, originally not how to start, but

/usr/local/mysql/support-files/mysql.server start

OK, now it can start normally, but some lazy readers find it necessary to add '/usr/local/mysql/bin ' and their troubles when they hit the command, can I use the ' MySQL xxx ' directly

This will add the MySQL command directory to the environment variable.

CP /etc/profile/root/backup//etc/profile

Edit '/etc/profile ', the above sentence is a backup to avoid the mistake, the system crashes can not be restored

At the tail Plus

Export Path=/usr/local/mysql/bin: $PATH

If you already have this sentence, just add a '/usr/local/mysql/bin: ' in front of the $path, and then make the configuration take effect immediately

Source/etc/profile

Use

Mysql-u Root

Go to the MySQL command line, these even cool, the rest of the match with this complete is OK

Then we will start the master-slave configuration, first configure the main library on the Centos4, modify the '/usr/local/mysql/my.cnf '

Server_id=1
Log_bin=mysql_bin

Then restart MySQL

Then add a user to the master library to synchronize the data

' Copier '@'%'12345678'

This user is called ' Copier ' password for ' 12345678 ' running extranet access

The rest of the configuration from the library, add the following sentence configuration

Log_bin=mysql_bin
Server-id=3

The rest is the same from the library configuration

Okay, so let's start connecting the main library.

On the MySQL command line, enter

Change MASTER tomaster_host='192.168.138.4', master_user=' Copier ' , Master_password='12345678', master_log_file=' mysql_bin.000004 ' , Master_log_pos=+;

There are two parameters in the Master_log_file and Master_log_pos to get to the main library, enter in the main library MySQL command line

Show Master Status\g

The results of File and Position represent the values of Master_log_file and Master_log_pos, and remember the single and comma above, and the last is the number without single quotation marks.

Then run under the MySQL command line from the library

Start slave

Then view the status

Show Slave Stauts\g

Can see

Must be this state, if the word "Connecting to master", then there is a problem, the problem arises in four aspects

1, the network does not pass

2, the user name password is not correct

3, Master_log_file and Master_log_pos values are not correct (each time the main library starts, both values may vary)

4, the main library firewall rules limit (I believe many students can not find the reason, it may be this, here the experimental environment to shut down the firewall is OK ' service iptables stop ')

OK, the above is my humble practice, the process encountered in the main problem is the firewall is not off, from the library state has been "connecting to master", closed after the basic solution

When I was looking for the question above, I found someone on the internet asking, how is it called read/write separation?

In fact, read and write separation is a structure, can only be written from the main library, write from the library closed, the program can be read from the main library or the main library read (generally from the library read), write can only select the main library, because most of the functions are read, multi-station from the library decentralized pressure. If you want to consider high availability, you can get two main libraries, a main library for hot standby, plus heartbeat monitoring and effective transfer, when the common main library failure, automatically switch to another standby main library, usually the two main libraries to keep the data consistent is OK. Of course, this thing involves something else, like hearbeat/drbd/keepalived these things.

Read-write separation has advantages, there are shortcomings, such as multiple databases may cause the program is more complex (load balancing problem), the most deadly is the replication delay (such as the next order, the user clearly has placed a single, but to the user Center but can not find, after a while will come out)

My humble Caishuxueqian, there are shortcomings, welcome to complement

MySQL5.6 implementation of Master-slave replication, read/write separation, decentralized single server pressure