Shulou(Shulou.com)05/31 Report--

This article mainly introduces the "MYSQL master-slave building method tutorial". In the daily operation, I believe that many people have doubts about the MYSQL master-slave building method tutorial. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "MYSQL master-slave building method tutorial". Next, please follow the editor to study!

1. MySQL master-slave replication building

The main steps of building MySQL master-slave replication are: installing mysql software, Master-side configuration deployment, Slave-side configuration deployment, and establishing master-slave synchronization.

Install operating system environment: REDHAT6.5

The following contents are tested by myself in the virtual machine.

1. Install MYSQLa, environment preparation and software installation

Installation environment:

NODE1 hostname master IP address 192.168.159.128

NODE2 hostname slave IP address 192.168.159.129

B. Install MYSQL master and slave.

# tar zxvf mysql-5.6.38-linux-glibc2.12-x86_64.tar.gz-C / usr/local/

# cd / usr/local/

# mv mysql-5.6.38-linux-glibc2.12-x86_64 mysql

# cd mysql

# groupadd mysql

# useradd-g mysql mysql

# passwd mysql

# cd / usr/local/

# chown-R mysql.mysql mysql

#. / script/mysql_install_db-user=mysql

# cd / usr/local/mysql

# chown-R root:root. /

# chown-R mysql:mysql data

# cp support-files/mysql.server / etc/rc.d/init.d/mysqld

# chmod uplix / etc/rc.d/init.d/mysqld

# chkconfig-add mysqld

# / etc/rc.d/init.d/mysqld start

Here is a simple installation. If you want to use it in a production environment and want to specify the required parameters, it is recommended to use source code installation.

Note: both master and slave use yum install to install the system's own mysql, which can also be used. The test is successful, but the version is relatively low.

2. Master configuration deployment a, add the following configuration under the [mysqld] node in the my.cnf configuration file on the main server

The yellow part is newly added.

Vi / etc/my.cnf

[mysqld]

Server-id=101

Log-bin=/var/lib/mysql/mysql-bin.log

Log-bin-index=/var/lib/mysql/mysql-bin.index

Expire_logs_days=30

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

User=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

Symbolic-links=0

[mysqld_safe]

Log-error=/var/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld.pid

Description:

Log-bin: give all the file base names of the binary log

Log-bin-index: gives the file name of the binary log file, usually starting with 000001, incremented sequentially. Full name: master-bin.000001

Server-id: the mysql server is unique ID and must be unique among all servers replicated from the master.

B. Create a user and grant permissions:

Create user repl_user

GRANT REPLICATION SLAVE ON *. * TO 'repl'@'%' IDENTIFIED BY PASSWORD' *'

You will encounter an error when setting the password:

ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number

Solution: use select password ('password you want to enter'); query the string corresponding to your password

Select password ('123456')

Found out is * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

GRANT REPLICATION SLAVE ON *. * TO 'repl_user'@'%' IDENTIFIED BY PASSWORD' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'

2. Configure deployment an on Slave end. Configuration parameters: [mysqld]

The yellow part is newly added.

Vi / etc/my.cnf

[mysqld]

Server-id=102

Log-bin=/var/lib/mysql/mysql-relay-bin.log

Log-bin-index=/var/lib/mysql/mysql-relay-bin.index

Relay_log_purge=on

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

User=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

Symbolic-links=0

[mysqld_safe]

Log-error=/var/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld.pid

3. Establish master-slave synchronization

(this method is used to rebuild the standby database, and the standby database and the main database are in a normal data synchronization state.)

To establish master-slave synchronization, you can export data from the master library or from an existing slave library, and then import it into a new slave library, and change master to establishes synchronization.

3.1. Export data

Export data on the main library:

Mysqldump-u***-p***-S / data/mysql6001/mysql.sock-- default-character-set=utf8-Q-- single-transaction-- master-data-A > / tmp/all_database.sql

(when rebuilding the standby database) you can also export data from the library: (this is a test environment, so it's all new databases for me, so it doesn't matter)

Mysqldump-u***-p***-S / data/mysql6001/mysql.sock-- default-character-set=utf8-Q-- single-transaction-- dump-slave-A > / tmp/all_database.sql

NOTES:

-- master-data and-- dump-slave export backups that contain master_log_file and master_log_pos information.

The function of the master-data option is to write binary information to the output file, in this case to the backup sql file.

-- the function of the dump-slave option is for the user to dump the data on the slave side and establish a new slave, at least the second slave, that is, there is already AME-> B. now, the data is exported from B to establish AME-- > C replication, which can be used not only to establish slave, but also for backup, to read binlog for roll forward starting point.

Example: (you can export the whole main database directly)

Mysqldump-uroot-- events-- all-databases > / opt/mysql.dump

3.2. Import data from the library

Mysql-upright bands *-paired bands *-- default-character-set=utf8 < all_database.sql

Example:

Mysql-uroot

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.