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

This article mainly shows you the "sample analysis of MySQL 5.5replication building", which is easy to understand and well organized. I hope it can help you solve your doubts. Let the editor lead you to study and study the "sample analysis of MySQL 5.5replication build".

-- Master 192.168.78.139

-- Slave 192.168.78.137

Install the MySQL software in Slave. For the installation process, please refer to the source code installation article.

Http://blog.itpub.net/26506993/viewspace-2072859/

-- Master shuts down the database and copies the data file to Slave

[root@localhost backup] # / software/bin/mysqladmin-usystem-paired MySQL 2015 'shutdown

[root@localhost backup] # 160426 19:50:32 mysqld_safe mysqld from pid file / var/run/mysqld/mysqld.pid ended

[1] + Done / software/bin/mysqld_safe-defaults-file=/etc/my.cnf (wd: / data)

(wd now: / backup)

[root@localhost backup] # ps-ef | grep 3306

Root 20319 55613 0 19:50 pts/2 00:00:00 grep 3306

[root@localhost /] # zip-r / install/mysql_data_20160427.zip / data/

[root@localhost install] # scp / install/mysql_data_20160427.zip root@192.168.78.137:/install/

The authenticity of host '192.168.78.137 (192.168.78.137)' can't be established.

RSA key fingerprint is 4a:41:41:4b:4b:83:ea:cc:4b:56:bb:20:0a:8c:88:ce.

Are you sure you want to continue connecting (yes/no)? Yes

Warning: Permanently added '192.168.78.137' (RSA) to the list of known hosts.

Root@192.168.78.137's password:

Mysql_data_20160427.zip 100% 5053KB 4.9MB/s 00:01

-- Slave, decompress the transferred data file to the data file directory to be used by Slave

[root@localhost install] # mkdir / mysql_data

[root@localhost install] # unzip-d / mysql_data/ / install/mysql_data_20160427.zip

[root@localhost install] # chown-R mysql.mysql / mysql_data/

-- Editing the configuration file of Master

[root@localhost install] # vim / etc/my.cnf

# Log

Server-id = 100

Log-bin = / log/binlog/mysql-bin

-- start the Mysql database of Master

[root@localhost backup] # / software/bin/mysqld_safe-- defaults-file=/etc/my.cnf &

[1] 20592

[root@localhost backup] # 160426 20:32:49 mysqld_safe Logging to'/ log/err.log'.

160426 20:32:49 mysqld_safe Starting mysqld daemon with databases from / data

-- create a dedicated replication account on the Master database

Mysql > grant replication slave on *. * to 'repl'@'192.168.78.%' identified by' Mysql#2015'

Query OK, 0 rows affected (0.04 sec)

-- View the current log name and location of Master for the following change master to commands in Slave

Mysql > flush tables with read lock

Query OK, 0 rows affected (0.00 sec)

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000008 | 256 |

+-+

1 row in set (0.00 sec)

-- configure the configuration file for Slave

[root@localhost data] # vim / etc/my.cnf

# Log

Server-id = 200

Log-bin = / log/binlog/mysql-bin

Relay-log = / log/binlog/mysqld-relay-bin

Relay-log-index = / log/binlog/product-mysql-relay-index

Datadir = / mysql_data/data

-- start Slave's database service

[root@localhost mysql] # / data/bin/mysqld_safe-- defaults-file=/etc/my.cnf &

[1] 22611

[root@localhost mysql] # 160426 22:53:18 mysqld_safe Logging to'/ log/err.log'.

160426 22:53:18 mysqld_safe Starting mysqld daemon with databases from / mysql_data/data

-- configure Slave to Master connection in Slave

The parameters that Slave connects to the Master server are set through the CHANGE MASTER TO statement to make Slave read the binary log of Master and the relay log of Slave.

Mysql > change master to

-> master_host='192.168.78.139'

-> master_port=3306

-> master_user='repl'

-> master_password='Mysql#2015'

-> master_log_file='mysql-bin.000008'

-> master_log_pos=256

Query OK, 0 rows affected (0.16 sec)

Parameter meaning:

Master_host specifies the connected Master host

Master_port specifies the port of the connected Master

Master_user specifies the replication dedicated account of the connected Master

Master_password specifies the password of the replication dedicated account of the connected Master

Master_log_file Master's current log name

Master_log_pos Master's current log location

-- Master releases global read-only lock

Mysql > unlock tables

Query OK, 0 rows affected (0.00 sec)

-- check the status of Slave

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State:

Master_Host: 192.168.78.139

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000008

Read_Master_Log_Pos: 256

Relay_Log_File: mysqld-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000008

Slave_IO_Running: No

Slave_SQL_Running: No

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: 256

Relay_Log_Space: 107

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: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 0

1 row in set (0.00 sec)

-- enable Slave's application log service

The START SLAVE statement starts two threads. The relay log O thread reads events from the Master server and stores them in the server. The SQL thread reads events from the relay log and executes them. SUPER permission is required to execute START SLAVE.

Mysql > start slave

Query OK, 0 rows affected (0.00 sec)

-- what is in the Slave log

[root@localhost data] # tailf / log/err.log

160427 5:57:08 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port='3306', master_log_file='', master_log_pos='4'. New state master_host='192.168.78.139'

Master_port='3306', master_log_file='mysql-bin.000010', master_log_pos='107'.

160427 5:57:23 [Note] Slave SQL thread initialized, starting replication inlog 'mysql-bin.000010' at position 107, relay log' / log/binlog/mysqld-relay-bin.000001' position: 4

160427 5:57:25 [Note] Slave I ramp O thread: connected to master 'repl@192.168.78.139:3306',replication started in log' mysql-bin.000010' at position

The above is all the contents of the article "sample Analysis of MySQL 5.5replication Building". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.