Linux Building master-slave database server (master-slave replication)

To configure the host database:

1. Cloning the Linux operating system

　　

　　

　　

　　

2. Modify the Linux system host IP address

Host ip:192.168.247.150

Slave machine ip:192.168.247.151

　　

3. Connect Master via Xshell

Connecting the host Ip:ssh 192.168.247.150

There may be a connection failure here.

Scenario One: Set the virtual machine's network adapter to a network connection

Scenario Two: Set the network IP in the local computer, set the virtual machine IP to the same network segment of the Linux system on the virtual machine.

　　

4. Install MySQL Database

1. Create a folder

CD (Space)/usr/local/src

2. Create MySQL Folder

mkdir MySQL

3. Go to MySQL folder

CD MySQL

4. Unzip MySQL (execute the following command sequentially)

1.TAR-XVF Percona-server-5.6.24-72.2-r8d0f85b-el6-x86_64-bundle.tar

2.RPM-IVH percona-server-56-debuginfo-5.6.24-rel72.2.el6.x86_64.rpm

3.RPM-IVH percona-server-shared-56-5.6.24-rel72.2.el6.x86_64.rpm

4. RPM-IVH percona-server-client-56-5.6.24-rel72.2.el6.x86_64.rpm

5.RPM-IVH percona-server-server-56-5.6.24-rel72.2.el6.x86_64.rpm

6. Import SQL Script--simply drag the SQL file into the edit box

5. Start the MySQL service

Service MySQL Start

This can be done through the MySQL command (because the user name and password have not been set in this step) to see if the following table is correct in the MySQL database. Then exit; Exit database

6. Open Port 3306

Iptables-i input-p TCP--dport 3306-j ACCEPT

7. Set User name and password

Mysqladmin-u root password "root"

8. Authorization command (executed after MySQL login in Xshell)

Grammar:

Grant [Permissions] on [database name]. [table name] to [' User name ']@[' Web server IP address '] identified by [' Password '];

Grant all on * * to ' root ' @ '% ' identified by ' root '; (recommended for this)

Or specify an IP address

Grant all on * * to ' root ' @ ' 192.168.1.103 ' identified by ' root ';

9. Connect 198.167.247.150 database on this host through visual tools.

　　　　

　　

To Configure the slave database:

The slave database is copied from the host, so just add the service number ID and add the binary log file (sixth step). The authorization of what is cloned over.

1. Before shutting down the host's MySQL service and then cloning the virtual machine

To clone and modify an IP address from a Slave

2, modify the Mysqlid from the library

Vim/var/lib/mysql/auto.cnf or Vim auto.cnf

Modify the UUID value (click I to enter the Modify mode, click the ESC key and enter: Wq return to save) the value of the UUID is changed to a place, and the host's Mysqlid area is OK

　　

3. Open the port number from the library

　　

Open 3306 Port: iptables-i input-p TCP--dport 3306-j ACCEPT

To view the open status of the current port:/etc/rc.d/init.d/iptables status

4. Open 192.168.150 Host virtual machine

First look at whether the port number is open, if not open first open, if opened directly to start the MySQL service.

Service MySQL Start

5. Verify that the MySQL database on the two database servers is connected through a visual tool

Databases on two machines, 192.168.247.150 and 192.168.247.151, respectively.

　　

6, the configuration of master-slave replication

1. Modify the configuration file of the main library (modify the service number of the main library)

Vim/etc/my.cnf

Press I to enter edit mode

Server-id=1

Log-bin=mysql-bin

Press ESC to exit and enter: Wq command to save

2. Restart MySQL

Stop MySQL Services service MySQL stop first

This step may have a Erro:

　　　　

Workaround:

Query MySQL service item first PS-EF |grep MySQL

　　　　　　

Then kill the extra process kill-9 1953 2,146 Extra Port number

3. Start the MySQL service

Service MySQL Start

　　

4. View master information for the main library

Show Master Status

　　

5. Attach from library to main library

1. Modify the service number from the library vim/etc/my.cnf

　　

The value of Server-id is self-determined. Here on the host is 1, from the machine for 2, if there are other slave to 3,4,5 ... This facilitates distinguishing between identification.

6. Restart the slave server

Stop before start

7. mount from the node to the master node

To execute a mount statement from a table visualization tool:

/* Configure the Note host name/port number/username/password/binary file name/synchronization location */

Change Master to master_host= '192.168.247.150', master_port=3306,

master_user= 'root', master_password= 'root',

Master_log_file= 'mysql-bin.000001', master_log_pos=

Note:

The red part is the place to be modified, and more practical information is added.

Binary file name and synchronization location The exact data that was isolated from the main library through the 4th step operation

8. Start the slave node

Start slave

Show slave status

　　

If 2 Yes is present, the above configuration runs successfully.

9. The test is successful

By creating a new test Database "AAA" database on the host database, if there is a "AAA" database after refresh of the slave database, the current database master-slave replication is successfully built!!!!

You can also add a record in the data table of the database specified by the main library, which also indicates a successful build if it exists in the data table from the same database as the library!!!!!!

Linux Building master-slave database server (master-slave replication)