What is the method of building MYSQL master and slave?
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
