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;

Related articles: