Detailed explanation of Mysql dual-machine Hot Standby installation in CentOS

Detailed explanation of Mysql dual-machine Hot Standby installation in CentOS

Mysql dual-machine Hot Standby installation document

1. Installation Environment and Resources
211.88.22.74 4 CPU 8G 120G hard drive centos6.5 _ 64
211.88.22.73 4 CPU 8G 120G hard drive centos6.5 _ 64
Two servers. Here we use 211.88.22.74 as the master server and 211.88.22.73 as the slave server.
1.1 mysql
A. Official:
Http://dev.mysql.com/downloads/mysql/#downloads
Or download an image file:
Http://dev.mysql.com/downloads/mirrors.html
In this example, the mysql-5.6.12-linux-glibc2.5-x86_64.tar.gz is used.

1. Install mysql on liunx
   2.1 installation steps:
   There are two main ways to install MySQL: one is to compile and install MySQL by yourself through the source code. This is suitable for advanced user customization of MySQL features, which are not described here; the other method is to install binary files that have been compiled. Binary File Installation can be divided into two methods: one is the generic Installation Method of the specific platform, the use of the binary file is suffixed with .tar.gz compressed file; the second is to use RPM or other packages for installation, this installation process will automatically complete the relevant system configuration, therefore, 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 whether the grep is installed. The-I option of grep indicates that case sensitivity is ignored when matching.
   [Root @ cloudview73 ~] # Rpm-qa | grep-I mysql
   Mysql-libs-5.1.61-4.el6.x86_64
   * It can be seen that the library file has been installed and should be uninstalled first. Otherwise, a overwriting error will occur. Note: The-nodeps option is used for loading, and dependency is ignored:
   [Root @ cloudview73 ~] # Rpm-e mysql-libs-5.1.61-4.el6.x86_64-nodeps
   Add a mysql group and a mysql user to configure the owner and group of the mysql installation directory file.
   [Root @ cloudview73 ~] # Groupadd mysql
   [Root @ cloudview73 ~] # Useradd-r-g mysql
   * The useradd-r parameter indicates that the mysql user is a system user and cannot be used to log on to the system.
   Unzip decompress the binary file to the specified installation directory, which is/usr/local.
   [Root @ cloudview73 ~] # Cd/usr/local/
   [Ncp @ cloudview73 local] $ tar-zxvf mysql-5.6.12-linux-glibc2.5-x86_64.tar.gz
   
   * After pressurization in/usr/local/generate a folder mysql-5.6.12-linux-glibc2.5-x86_64 that understands the press, this name is too long and we create a symbolic link for it to mysql for ease of input.
   [Root @ cloudview73 ~] # Ln-s mysql-5.6.12-linux-glibc2.5-x86_64 mysql
   
   Directory structure under logs/usr/local/mysql/
   
   Enter the mysql folder, that is, the directory where mysql is located, and change the group and user.
   [Ncp @ cloudview73 local] Cdmysql [ncp @ cloudview73local] Chown-R mysql.
   [Ncp @ cloudview73 local] $ chgrp-R mysql.
   Note: Do not forget the next point; otherwise, an error will be reported.
    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 will access the data directory during running. Therefore, you must run this script by the user who started the mysqld process (that is, the mysql user we set earlier), or use root to execute the script, however, the parameter-user = mysql is added.
   [Root @ localhost mysql] scripts/mysql_install_db-user = mysql
   * If the mysql installation directory (extract directory) is not/usr/local/mysql, you must specify the directory parameters, as shown in figure
   [Root @ localhost mysql] scripts/mysql_install_db-user = mysql \
   -Basedir =/opt/mysql \
   -Datadir =/opt/mysql/data
   * Change all files except the data/directory under the mysql/directory to the root user. The mysql user only needs to be the owner of all files under the mysql/data/directory.
   [Root @ localhost mysql] chown-R root.
   [Root @ localhost mysql] chown-R mysql data
    Copy the configuration file
   [Root @ localhost mysql] cp support-files/my-default.cnf/ect/my. cnf
    Add the mysqld service to the startup Item.
   * Copy the scripts/mysql. server Service script to/etc/init. d/and rename it mysqld.
   [Root @ localhostmysql] cp support-files/mysql. server/etc/init. d/mysqld
   * Use the chkconfig command to add the mysqld service to the self-starting service item.
   [Root @ localhost mysql] # chkconfig-add mysqld
   * Note that the service name mysqld is the name we renamed when copying mysql. server to/etc/init. d.
   * Check whether the configuration is successfully added.
   [Root @ localhost mysql] # chkconfig-list mysqld

Mysqld 0: off
1: off 2: on 3: on 4: on 5: on 6: off
I. restart the system and mysqld will automatically start.
* Check whether startup is enabled.

Root @ localhost 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 restart the instance, you can manually start the instance.
[Root @ localhost mysql] # service mysqld start
Starting MySQL... SUCCESS!
J. Run the mysql client program. In the mysql/bin directory, test whether the client can connect to mysqld.
[Root @ localhost 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) 2000,201 2, Oracle and/or its affiliates. All rights reserved.
Oracle is a registeredtrademark of Oracle Corporation and/or its affiliates. Other names may betrademarks of their respective owners.
Type 'help; 'or' \ H' forhelp. Type '\ C' to clear the current input statement.
Mysql> quit
Bye
* Mysql> command prompt appears. You can enter an SQL statement, quit, or exit to exit. To avoid entering the full path/usr/local/mysql/bin/mysql of mysql every time, you can add it to the environment variable and add the following two lines at the end of/etc/profile:
MYSQL_HOME =/usr/local/mysql
Export PATH = PATH: MYSQL_HOME/bin
In this way, you can directly enter the mysql command in shell to start the client program.
[Root @ localhost 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) 2000,201 2, Oracle and/or its affiliates. All rights reserved.
Oracle is a registeredtrademark of Oracle Corporation and/or its
Affiliates. Other namesmay be trademarks of their respective
Owners.
Type 'help; 'or' \ H' forhelp. Type '\ C' to clear the current input statement.
Mysql>
The installation of the ghost source package is complete.
3. Install mysql in rpm Mode
4. Check whether the grep is installed. The-I option of grep indicates that case sensitivity is ignored when matching.
5. [root @ localhost JavaEE] # rpm-qa | grep-I mysql
6. mysql-libs-5.1.61-4.el6.x86_64
7. It can be seen that the library file has been installed and should be uninstalled first. Otherwise, a overwriting error will occur. Note that the-nodeps option is used during uninstallation, and the dependency is ignored:
8. [root @ localhost JavaEE] # rpm-e mysql-libs-5.1.61-4.el6.x86_64-nodeps
. Install MySQL server software. Switch to the root user:
10. [root @ localhost JavaEE] # rpm-ivh
After the MySQL-server-5.6.12-2.el6.x86_64.rpm is installed, the installation process adds a mysql group in Linux and the user mysql that belongs to the mysql group. You can use the id command to View Details:
11. [root @ localhost JavaEE] # id mysql
12. uid = 496 (mysql) gid = 493 (mysql) groups = 493 (mysql)
13. After the MySQL server is installed, although related files are configured, The mysqld service is not automatically started. You need to start the service on your own:
14. [root @ localhost JavaEE] # service mysql start
15. Starting MySQL... SUCCESS!
16. Check whether the port is enabled to check whether MySQL is started properly:
17. [root @ localhost JavaEE] # netstat-anp | grep 3306
18. tcp 0 0 0.0.0.0: 3306 0.0.0.0: * LISTEN 34693/mysqld
19. c. Install MySQL client software:
20. [root @ localhost JavaEE] # rpm-ivh MySQL-client-5.6.12-6.x86_64.rpm
21. If the installation is successful, you can run the mysql Command. Note that the mysqld service must be enabled:
22. [root @ localhost JavaEE] # mysql
23. Welcome to the MySQLmonitor. Commands end with; or \ g.
24. Your MySQL connection idis 1
25. Server version: 5.5.29MySQL Community Server (GPL)
26. Copyright (c) 2000,201 2, Oracle and/or its affiliates. All rights reserved.
27. Oracle is a registered trademarkof Oracle Corporation and/or its affiliates. Other names may be trademarks oftheir respective owners.
28. Type 'help; 'or' \ H' forhelp. type' \ C' to clear the current input statement.
29. mysql>

 RPM Installation Method file distribution

Mysql installation is complete. Mysql is installed on both the master server and slave server.

4. Start Master/Slave settings
Configure the MySQL master server (211.88.22.74)
Go to the mysql operation interface and create a connection account for the SLAVE server on the master server. This account must be granted the replication slave permission. Since mysql 3.2 and later versions, you can use REPLICATION to perform hot standby on two machines.
The operation commands are as follows:
Mysql> grant replication slave on.To 'iec '@ '211.88.22.74' identified by 'iec2016
Mysql> flush privileges;
After creating a synchronization connection account, we can use the replicat account on the Slave server (Slave) to access the Master server (Master) database to check whether the connection is successful.
Enter the following command on the Slave server (Slave:
[Root @ YD146 ~] # Mysql-h 211.88.22.74-uiec-piec2016
If the following result is displayed, the logon is successful, indicating that the two servers can be hot-standby.

Modify mysql configuration file
If the above preparations are complete, we can modify the mysql configuration file over there. First, find all the mysql configurations in the directory. Generally, after installing the mysql service, the configuration files will be copied one by one and put under the/ect directory, and the configuration file name is: my. cnf. The correct configuration file directory is/etc/my. cnf.
Find the configuration file my. cnf and modify it under [mysqld:
Server-id = 1
Log-bin = mysql-bin // these two lines are originally available. You can add the following two lines without moving them.
Binlog-do-db = cieccNcp
Binlog-ignore-db = mysql

Restart mysql Service
After the configuration file is modified, save the file and restart the mysql service. If the configuration file is successfully modified, no problem occurs.

 View master server status
After entering the mysql service, you can run the command to view the Master Status and enter the following command:

Pay attention to the parameters, especially the first two files and positions, which are used to configure the master-Slave relationship on the Slave server (Slave.
Note: The lock table is used here to prevent new data from being imported into the production environment, so that the synchronization location can be located from the server. Remember to unlock it after the initial synchronization is complete.

Slave server Slave Configuration
Modify configuration file
Because mysql dual-host hot backup is implemented in master-slave mode, you do not need to create a synchronization account on the slave server. Open the configuration file my. cnf can be modified. The principle is the same as modifying the master server, but the parameters to be modified are different. As follows:
[Mysqld]
Server-id = 2
Log-bin = mysql-bin
Replicate-do-db = cieccNcp
Replicate-ignore-db = mysql
Restart mysql Service

Using the change mster statement to specify the synchronization location
This step is the most critical step. after entering the mysql operation interface, enter the following command:
Mysql> stop slave; // It is very important to stop the slave service thread first. Otherwise, the following operations may fail.
Mysql> change master

Master_host = '1970. 88.22.74 ', master_user = 'iec', master_password = 'iec2016 ',
Master_log_file = 'mysql-bin.000016 ', master_log_pos = 107;
Note: master_log_file and master_log_pos are determined by the status value detected by the Master server (Master. That is, just called attention. Master_log_file corresponds to File and master_log_pos corresponds to Position. Mysql 5.x and later versions do not support specifying the primary server-related options in the configuration file.
If the following problem occurs after performing the preceding steps:

You need to reset the slave. The command is as follows:
Mysql> stop slave;
Mysql> reset slave;
Then stop the slave thread and start again. After success, you can enable the slave thread.
Mysql> start slave;
Slave to view the Slave server (Slave) Status
The status in the red box indicates that the configuration is successful.

Check that the values below are Yes, indicating that the slave server is successfully set.
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
The master-slave configuration is complete. The following is a test.
Test Synchronization

We have already said that there is no data in the database cieccNcp only one table tb_mobile. We can first check whether the databases on the two servers have data:

On the master server: 211.88.22.74

Slave server: 211.88.22.73

Currently, the master and slave servers are empty.
We can insert a piece of data on the master server to see if the data is synchronized:

Next, let's see if the data is backed up from the server:

We can see from the above two that the data inserted on the Master server can be found on the Slave server, which indicates that the hot standby configuration is successful.

Zhongying Chinese and Western software technology park-wanzuwodou !!!!!!!!!!!!!!!!!!