centos under mysql master slave synchronization quick setup step sharing
- 2020-05-13 03:33:36
- OfStack
Installation environment
centos 5.4
mysql 5.1.xx is installed directly with rpm
xtrabackup 1.2.22 is installed directly with rpm
1. Master:/etc/my.cnf
[mysqld] server-id =1 log_flush_at_trx_commit =1 sync_binlog=1 datadir=/var/lib/mysql character-set-server ='SET NAMES utf8' sets the default character set to utf8, and you can choose this configuration according to the actual situation.
2. Slave:/etc/my.cnf
[mysqld] server-id=2 datadir=/var/lib/mysql character-set-server=utf8 init_connect='SET NAMES utf8'
3. Master: set the slave user permissions for synchronization in the master database
GRANT REPLICATION SLAVE ON *.* TO ' < slave_username > '@' < slave_ip > ' IDENTIFIED BY ' < slave_password > ';
4. Master: export data to slave
xtrabackup is used to back up mysql. The advantage is that the lock table in master is very short and can be used in a real production environment, and xtrabackup will automatically record the location of synchronous log files.
sudo innobackupex-1.5.1 --stream=tar /tmp/ | ssh < slave_host > "mkdir/tmp/db; tar xfi - -C /tmp/db/" this step will export and compress the entire master data, including the table structure, to slave and unzip it to slave's /tmp/db directory.
5. Slave: import data to slave
innobackupex-1.5.1 --apply-log /tmp/db innobackupex-1.5.1 --copy-back /tmp/db chown -R mysql.mysql /var/lib/mysql/*
6. Slave: start synchronizing data
View/var/lib/mysql/xtrabackup_binlog_info, get the log file and position.
CHANGE MASTER TO MASTER_HOST=' < master_host > ', MASTER_USER=' < slave_username > ', MASTER_PASSWORD=' < slave_password > ', MASTER_LOG_FILE=' < see xtrabackup_binlog_info > ', MASTER_LOG_POS= < see xtrabackup_binlog_info > ; START SLAVE;
centos 5.4
mysql 5.1.xx is installed directly with rpm
xtrabackup 1.2.22 is installed directly with rpm
1. Master:/etc/my.cnf
[mysqld] server-id =1 log_flush_at_trx_commit =1 sync_binlog=1 datadir=/var/lib/mysql character-set-server ='SET NAMES utf8' sets the default character set to utf8, and you can choose this configuration according to the actual situation.
2. Slave:/etc/my.cnf
[mysqld] server-id=2 datadir=/var/lib/mysql character-set-server=utf8 init_connect='SET NAMES utf8'
3. Master: set the slave user permissions for synchronization in the master database
GRANT REPLICATION SLAVE ON *.* TO ' < slave_username > '@' < slave_ip > ' IDENTIFIED BY ' < slave_password > ';
4. Master: export data to slave
xtrabackup is used to back up mysql. The advantage is that the lock table in master is very short and can be used in a real production environment, and xtrabackup will automatically record the location of synchronous log files.
sudo innobackupex-1.5.1 --stream=tar /tmp/ | ssh < slave_host > "mkdir/tmp/db; tar xfi - -C /tmp/db/" this step will export and compress the entire master data, including the table structure, to slave and unzip it to slave's /tmp/db directory.
5. Slave: import data to slave
innobackupex-1.5.1 --apply-log /tmp/db innobackupex-1.5.1 --copy-back /tmp/db chown -R mysql.mysql /var/lib/mysql/*
6. Slave: start synchronizing data
View/var/lib/mysql/xtrabackup_binlog_info, get the log file and position.
CHANGE MASTER TO MASTER_HOST=' < master_host > ', MASTER_USER=' < slave_username > ', MASTER_PASSWORD=' < slave_password > ', MASTER_LOG_FILE=' < see xtrabackup_binlog_info > ', MASTER_LOG_POS= < see xtrabackup_binlog_info > ; START SLAVE;