Shulou(Shulou.com)06/01 Report--

mysql replicaton

Environment: Master: 192.168.48.150

From: 192.168.48.152

Simply put, master writes database changes to binary logs, slave synchronizes these binary logs, and performs data operations based on these binary logs.

Use of mysql Replicaton

1.fail over

2.backup server

3.high performance

The data on the master and slave are the same, so if we write from the master and read from the slave, this is called read-write separation.

configure Replication

step1:

Slave is a binary log that logs onto Master via MYSQL connection. Therefore, you need to configure permissions for Slave on the Master.

mysql>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slaveip' IDENTIFIED BY 'replPass';

mysql>FLUSH PRIVILEGES;

eg: Authorized on Master:

mysql> grant replication slave on *.* to repl@192.168.48.152 identified by 'repl';

mysql> flush privileges;

Test the connection on slave:

#mysql -h292.168.48.150 -urepl -prepl

It can be connected to indicate that there is no problem.

step2:

On the Master, open the binary log and identify server-id.

server-id is used to uniquely identify the host, and the numerical value range is 1-2**32-1.

#vi /etc/my.cnf

[mysqld]

log-bin

binlog-format=row

sync-binlog=1

server-id=1

#service mysqld restart

step3:

Make a full backup of the Master and perform prepare.

#The principle here is to package the main original data backup to the slave, restore it directly from the server, and then synchronize the binary transmission on this basis, which will save resources.

We use innobackups, a tool that records the binary number of backup data so that the slave server synchronizes from the backup, i.e. incremental backups.

#innobackupex --user=dba --password=xxx/var/lib/backup/

#innobackupex --use-memory=500m --apply-log /var/lib/backup/2014-03_16-40-05/

eg: configuration on master

#innobackupex install: my.oschina.net/sansom/blog/160434

# mkdir /var/lib/backup/

# cd /var/lib/backup/

#innobackupex --user=root --password=123456 /var/lib/backup/

#This will generate the original file in the directory

#One more step is to generate innodb log files

#innobackupex --use-memory=500m --apply-log /var/lib/backup/original filename/

step4:

Copy the backup to slave and place it in the data file directory.

in master:

#yum install openssh-clients -y (master and slave both need to install)

#scp -r /var/lib/backup/2016-03-11_10-26-10/ 192.168.48.152:/var/lib/backup/

in slave:

#service mysqld stop

#mv /var/lib/backup/2016-03-11_07-40-35/* /var/lib/mysql/(Do not overwrite directly in production environments)

#ls(check to see if all files have been mv passed, if not, copy them again)

#cd /var/lib/mysql/

#chown -R mysql. * #Change permissions for copied files

#service mysqld start (my mysql does not start at this step because selinux is not closed)

#Check if the data is synchronized correctly

step5:

Configure server-id on slave to identify the server.

# vi /etc/my.cnf

[mysqld]

server-id=2

#service mysqld restart

step6:

View and record the position ID in the binary log. (This was generated for us by the innobackupex tool)

in slave:

#cd /var/lib/mysql

cat xtrabackup_binlog_info

Display: mysqld-bin.000001 341

Position ID is 341

step7:

Configure slave replication.

in slave:

mysql>change master to master_host='192.168.48.150',

-->master_user='repl',

-->master_password='repl',

-->master_log_file='mysqld-bin.000001',

-->master_log_pos=341;

step8:

Start replication and check the results.

in slave:

mysql>start slave;

mysql>show slave status\G;

The results were as follows:

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.48.150 (Master ip)

Master_User: repl

Master_Port: 3306

Connect_Retry: 60 (retry time/s)

Master_Log_File: mysqld-bin.000001

Read_Master_Log_Pos: 341

Relay_Log_File: mysqld-relay-bin.000002 (from binary file)

Relay_Log_Pos: 252 (slave position number)#Although the number of master and slave is different, there is a corresponding relationship, logically the same.

Relay_Master_Log_File: mysqld-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

#The master will launch one process, and the slave will launch two processes. We can execute the following statement to view:

master_mysql>show processlist\G;

*************************** 1. row ***************************

Id: 11

User: repl

Host: 192.168.48.152:54413

db: NULL

Command: Binlog Dump

Time: 707

State: Has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

*************************** 2. row ***************************

slave_mysql>show processlist\G;

*************************** 2. row ***************************

Id: 3

User: system user

Host:

db: NULL

Command: Connect

Time: 680

State: Waiting for master to send event

Info: NULL

*************************** 3. row ***************************

Id: 4

User: system user

Host:

db: NULL

Command: Connect

Time: 680

State: Has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

3 rows in set (0.00 sec)

#Two processes from, one for receiving binary and the other for parsing binary, execute statements.

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 341

Relay_Log_Space: 408

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

1 row in set (0.00 sec)

This synchronization, in the master operation, from will be synchronized.

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.