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

This article mainly explains "what is the method of building MYSQL master and slave". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the method of building MYSQL master and slave".

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.

1. Install MYSQLa, environment preparation and software installation

Application requirements:

Dual-computer hot backup provides backup and redundancy

Installation environment:

NODE1 hostname master IP address 192.168.159.128

NODE2 hostname slave IP address 192.168.159.129

VIA IP (drift IP) 10.10.10.100

NODE1 master node, NODE2 slave node, synchronous database name fire9

Please make sure that the following installation package does not exist before installation

Rpm-e mysql-devel-4.1.20-1.RHEL4.1

Rpm-e mysql-bench-4.1.20-1.RHEL4.1

Rpm-e php-mysql-4.3.9-3.15

Rpm-e libdbi-dbd-mysql-0.6.5-10.RHEL4.1

Rpm-e mod_auth_mysql-2.6.1-2.2

Rpm-e mysql-server-4.1.20-1.RHEL4.1

Rpm-e MySQL-python-1.0.0-1.RHEL4.1.i386

Rpm-e MyODBC-2.50.39-21.RHEL4.1.i386

Rpm-e qt-MySQL-3.3.3-9.3.i386

Rpm-e mysqlclient10-devel-3.23.58-4.RHEL4.1.i386

Rpm-e mysqlclient10-3.23.58-4.RHEL4.1

Rpm-e cyrus-sasl-sql-2.1.19-5.EL4.i386

Rpm-e perl-DBD-MySQL-2.9004-3.1.i386

Rpm-e mysql-4.1.20-1.RHEL4.1

Installation preparation: I have put the relevant software and configuration files in the toolkit

Redhat as 4 update4 32 bit

Mysql-5.0.45-linux-i686-icc-glibc23.tar.gz

Libnet-1.1.2.1-1.rh.el.um.1.i386.rpm

Heartbeat-pils-2.0.4-1.el4.i386.rpm

Heartbeat-stonith-2.0.4-1.el4.i386.rpm

Heartbeat-2.0.4-1.el4.i386

Perl-5.8.8.tar.gz

DBI-1.59.tar.gz

DBD-mysql-4.005.tar.gz

Time-HiRes-01.20.tar.gz

Period-1.20.tar.gz

Convert-BER-1.31.tar.gz

Mon-0.11.tar.gz

Mon-0.99.3-47.tar.gz

B. Install MYSQL master and slave.

# tar zxvf mysql-5.0.45-linux-i686-icc-glibc23.tar.gz-C / usr/local/

# cd / usr/local/

# mv mysql-5.0.45-linux-i686-icc-glibc23 mysql

# cd mysql

# groupadd mysql

# useradd-g mysql mysql

# passwd mysql

#. / scripts/mysql_install_db-user=mysql

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

# chmod + x / etc/rc.d/init.d/mysqld

# chkconfig-add mysqld

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

Note: both master and slave use yum install to install the mysql that comes with the system, but the version will be relatively low.

2. Master-side 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 also used to rebuild the standby library.)

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

(or) export data from the library:

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.

Example:

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.