MySQL dual-Machine hot-Standby installation detailed under CentOS

MySQL dual-Machine hot standby Installation documentation

1. Installation Environment and resources
211.88.22.74 4CPU 8G 120G HDD centos6.5_64
211.88.22.73 4CPU 8G 120G HDD centos6.5_64
Server Two. Here 211.88.22.74 is used as the primary server, 211.88.22.73 as slave server.
1.1 MySQL
A. Official:
http://dev.mysql.com/downloads/mysql/#downloads
or image file Download:
Http://dev.mysql.com/downloads/mirrors.html
This example installs using: mysql-5.6.12-linux-glibc2.5-x86_64.tar.gz.

1. Install MySQL on Liunx
   2.1 Installation steps:
   There are two main ways to install MySQL: one is self-compiling through the source of the installation, this is suitable for advanced user custom MySQL features, not to do this, and the other is to install through the compiled binary files. Binary file installation methods are divided into two types: one is not for the specific platform of the common installation method, the use of the binary file is the suffix. tar.gz of the compressed file, the second is to use the RPM or other packages for installation, the installation process will automatically complete the configuration of the system, so it is more convenient.
   A. General installation method
   Mysql-5.6.12-linux-glibc2.5-x86_64.tar.gz
   b.rpm Installation Method:
   mysql-5.6.12-linux-glibc2.5-x86_64.rpm
   mysql-5.6.12-linux-glibc2.5-x86_64.rpm
   2.1.1 General installation Steps
   ? Check if it is installed, grep's-i option indicates ignore case when matching
   [[Email protected] ~] #rpm-qa|grep-i MySQL
   Mysql-libs-5.1.61-4.el6.x86_64
   * You can see that the library file has been installed, should be uninstalled first, or there will be overwrite errors. Note unloading: The –nodeps option was used when loading, ignoring the dependency relationship:
   [[Email protected] ~] #rpm-e mysql-libs-5.1.61-4.el6.x86_64–nodeps
   ? Add MySQL group and MySQL user to set the MySQL installation directory file owner and owning group.
   [[Email protected] ~] #groupadd MySQL
   [[Email protected] ~] #useradd-R-G MySQL MySQL
   The *useradd-r parameter indicates that the MySQL user is a system user and cannot be used to log on to the system.
   ? Extract the binaries to the specified installation directory, which we specify as/usr/local
   [Email protected] ~]# cd/usr/local/
   [Email protected] local]$ TAR-ZXVF mysql-5.6.12-linux-glibc2.5-x86_64.tar.gz
   
   * After pressurization in/usr/local/generate understand the pressure after the folder mysql-5.6.12-linux-glibc2.5-x86_64, this name is too long, we create a symbolic link for it mysql, convenient input.
   [[Email protected] ~] #ln-S mysql-5.6.12-linux-glibc2.5-x86_64 MySQL
   
   ? Directory structure under/usr/local/mysql/
   
   ? Go to the MySQL folder, the directory where MySQL resides, and change the groups and users that belong to it.
   [Email protected]w73 Local]CDMYSQL[Ncp @ cl ou dv ie w73 lo ca l] Chown-r MySQL.
   [[email protected] local]$ chgrp-r MySQL.
   Note: Do not forget the point at the back, otherwise you will get an error.
   ? Execute the mysql_install_db script to initialize the data directory in MySQL and create some system tables. Note that the MySQL service process mysqld accesses the data directory, so it must be executed by the user who started the mysqld process (the MySQL user we set up earlier), or by root, but with the parameter –user=mysql.
   [Email protected] Mysql]scripts/mysql_install_db–user=mysql
   * If the MySQL installation directory (extract directory) is not/usr/local/mysql, then you must also specify the directory parameters, such as
   [email protected] mysql]scripts/mysql_install_db–user=mysql \
   –basedir=/opt/mysql/mysql \
   –datadir=/opt/mysql/mysql/data
   * All files in the mysql/directory except the data/directory are changed back to the root user, and the MySQL user only needs to be the owner of all files in the mysql/data/directory.
   [[email protected] mysql]chown-r root.
   [[email protected] mysql]chown-r MySQL data
   ? Copying a configuration file
   [[email protected] MySQL] CP support-files/my-default.cnf/ect/my.cnf
   ? Add the Mysqld service to the power-on self-boot entry.
   * First, you need to copy the Scripts/mysql.server service script to/etc/init.d/and rename it to Mysqld.
   [[email protected]] CP Support-files/mysql.server/etc/init.d/mysqld
   * The MYSQLD service is added to the self-starting service item via the Chkconfig command.
   [[email protected] MySQL] #chkconfig –add mysqld
   * Note Service name Mysqld is the name that we renamed when we copied Mysql.server to/etc/init.d/.
   * See if Add success
   [[email protected] MySQL] #chkconfig –list mysqld

Mysqld 0:off
1:off 2:on 3:on 4:on 5:on 6:off
I. Restart the system and the mysqld will start automatically.
* Check whether to start

[email protected] MySQL] #netstat-anp|grep mysqld
TCP 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2365/mysqld
UNIX 2 [ACC] STREAM LISTENING 14396 2365/mysqld/tmp/mysql.sock
* If you do not want to reboot, you can start it manually.
[[email protected] MySQL] #service mysqld start
Starting MySQL. success!
J. Run the client program MySQL, and in the Mysql/bin directory, test whether you can connect to mysqld.
[Email protected] Mysql]#/usr/local/mysql/bin/mysql
Welcome to the Mysqlmonitor. Commands End With; or \g.
Your MySQL Connection Idis 2
Server Version:5.5.29-log MySQL Community Server (GPL)

Copyright (c), 2012,oracle and/or its affiliates. All rights reserved.
Oracle is a registeredtrademark of the Oracle Corporation and/or its affiliates. Other names Betrademarks of their respective owners.
Type ' help; ' or ' \h ' forhelp. Type ' \c ' to clear the current input statement.
Mysql> quit
Bye
* The mysql> command prompt appears, you can enter the SQL statement, enter quit or exit. To avoid entering MySQL's full path/usr/local/mysql/bin/mysql each time, add it to the environment variable, and then add two lines of command to the/etc/profile:
Mysql_home=/usr/local/mysql
Export path=PATH: Mysql_home/bin
This allows you to start the client program by entering the MySQL command directly in the shell.
[[email protected] MySQL] #mysql
Welcome to the Mysqlmonitor. Commands End With; or \g.
Your MySQL Connection Idis 3
Server Version:5.5.29-log MySQL Community Server (GPL)
Copyright (c), 2012,oracle and/or its affiliates. All rights reserved.
Oracle is a registeredtrademark of the Oracle Corporation and/or its
Affiliates. Other Namesmay is trademarks of their respective
Owners.
Type ' help; ' or ' \h ' forhelp. Type ' \c ' to clear the current input statement.
Mysql>
? Source package to this installation is complete.
3. Install MySQL in rpm mode
4. Check if it is installed, grep's-i option indicates ignore case when matching
5. [[email protected] Java EE] #rpm-qa|grep-i MySQL
6. mysql-libs-5.1.61-4.el6.x86_64
7. It can be seen that the library file is installed, should be uninstalled first, or there will be overwrite errors. Note The –nodeps option is used when uninstalling, ignoring dependencies:
8. [[email protected] Java EE] #rpm-e mysql-libs-5.1.61-4.el6.x86_64–nodeps
9.2. Install the MySQL server-side software, and note that you switch to the root user:
[[email protected] Java EE] #rpm –IVH
After the mysql-server-5.6.12-2.el6.x86_64.rpm installation is complete, the installation process adds a MySQL group to Linux and MySQL for users who belong to the MySQL group. Can be viewed by the ID command:
[[email protected] Java EE] #id MySQL
uid=496 (MySQL) gid=493 (MySQL) groups=493 (MySQL)
MySQL server after installation, although the relevant files are configured, but do not automatically start the MYSQLD service, you need to start it yourself:
[[email protected] Java EE] #service mysql start
Starting MySQL. success!
16. Check to see if MySQL is booting properly by checking that the port is turned on:
[[email protected] Java EE] #netstat-anp|grep 3306
TCP 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 34693/mysqld
C. Installing the MySQL client software:
[[email protected] Java EE] #rpm-ivh mysql-client-5.6.12-6.x86_64.rpm
21. If the installation is successful, you should be able to run the MySQL command, note that it must be mysqld service and enabled:
[[email protected] Java EE] #mysql
Welcome to the Mysqlmonitor. Commands End With; or \g.
Your MySQL Connection Idis 1
Server Version:5.5.29mysql Community Server (GPL)
Copyright (c), 2012,oracle and/or its affiliates. All rights reserved.
Oracle is a registered TRADEMARKOF Oracle Corporation and/or its affiliates. Names may trademarks Oftheir respective owners.
Type ' help; ' or ' \h ' forhelp. Type ' \c ' to clear the current input statement.
Mysql>.

? RPM Installation mode file distribution

To this MySQL installation is complete. Both the primary server and the slave server are installed on MySQL.

4. Start the master-slave setup  
Configure the MySQL master server (211.88.22.74)  
Enter the MySQL operator interface and establish a connection account on the primary server for the slave server, which must be granted replication Slave permissions. Since MySQL version 3.2 can be used by the replication to the dual-machine hot standby function operation.  
operation directives are as follows:  
mysql> grant replication slave on  .  to ' IEC ' @ ' 211.88.22.74 ' identified by ' iec2016 
mysql> flush privileges; 
After you create a sync connection account, We can see if the connection succeeds by accessing the primary server (master) database with the Replicat account on the slave server (Slave).  
Enter the following command on the slave server (Slave):  
[[email protected] ~]# mysql-h 211.88.22.74-uiec–piec2016 
If the following results appear, you can log on successfully, indicating that both servers are ready to operate on a two-machine hot standby.  
 
? Modify the MySQL configuration file  
If the above preparation is done, we can make changes to the MySQL configuration file, first find the MySQL configuration all in the directory, generally after installing the MySQL service , the configuration file is copied one by one copies out of the/ect directory, and the configuration file is named: my.cnf. That is, the configuration file is the exact directory/etc/my.cnf 
After locating the configuration file my.cnf Open, modify it under [mysqld]:  
Server-id = 1 
Log-bin=mysql-bin These two lines are the original, you can not move, add the following two lines can be  
binlog-do-db = cieccncp 
binlog-ignore-db = mysql 

? Restart MySQL Service
After modifying the configuration file, after saving, restart the MySQL service, if successful, no problem.

? View Primary Server Status
After entering the MySQL service, the command can be used to view the master status and enter the following command:

Note the parameters inside, especially the first two file and position, which can be useful to configure the master-slave relationship from the server (Slave).
Note: The lock table is used here, in order to generate the environment in the new data, so that from the server location synchronization location, the initial synchronization is completed, remember to unlock.

Slave configuration from server
Modifying a configuration file
Because this is in the main-from the way to achieve the MySQL dual-machine hot standby, so in the slave server is not in the establishment of synchronization account, directly open the configuration file my.cnf to modify, the same reason is the same as the modification of the main server, just need to modify the parameters are different. As follows:
[Mysqld]
Server-id = 2
Log-bin=mysql-bin
Replicate-do-db =CIECCNCP
replicate-ignore-db = MySQL
Restart MySQL Service

? Specifying a synchronization location with the change Mster statement
This step is the most critical step, after entering the MySQL operator interface, enter the following command:
Mysql>stop slave; It is important to stop the slave service thread first, and if you do not do this it will cause the following operations to be unsuccessful.
Mysql>change Master to

Master_host= ' 211.88.22.74 ', master_user= ' IEC ', master_password= ' iec2016 ',
Master_log_file= ' mysql-bin.000016 ', master_log_pos=107;
Note: Master_log_file, Master_log_pos is determined by the status value identified by the master server (master). It's just called attention. Master_log_file corresponds to file, Master_log_pos corresponds to position. Mysql 5.x or later does not support the option to specify the primary server in the configuration file.
If you follow the above steps, you will see the following situation:

To reset the slave. Instructions are as follows
Mysql>stop slave;
Mysql>reset slave;
Then stop the slave thread to start again. After success, you can turn on the slave thread.
Mysql>start slave;
? View from server (Slave) status
The presence of a status in the Red box indicates a successful configuration.

Viewing the following two key values are yes, which means that the setting is successful from the server.
Slave_io_running:yes
Slave_sql_running:yes
The master configuration is complete. Test it below.
Test synchronization

之前开始已经说过了在数据库cieccNcp只有一个表tb_mobile没有数据，我们可以先查看下两服务器的数据库是否有数据：

On the primary server: 211.88.22.74

From the server: 211.88.22.73

Now the master and slave servers are empty.
We insert a piece of data on the primary server to see if it is synchronized:

Below we are looking at whether the data is backed up from the server:

As can be seen from the above two, the data inserted on the master server can be found on the slave server, which indicates that the dual-machine hot standby configuration is successful.

Ding Feng Xiao Hu
qq.2881064155
[Email protected]

MySQL dual-Machine hot-Standby installation detailed under CentOS