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

Mysql 5.5 multiple instances of databases used in the environment

Mysql master-slave copy schematic diagram

Mysql master-from-replication configuration environment is one database, multiple instances, 3306 port master, 3307 port slave.

1 start configuring my.cnf configuration files for master and slave libraries

Configure the my.cnf file for the primary database (3306)

Server-id = 1

Log-bin=mysql-bin

Configure the my.cnf file from the database (3307)

Server-id = 2

# log-bin=mysql-bin

Then restart the database

2 Log in to the master database to assign permission users to the slave database

Grant replication slave on *. * to 'repl'@'192.168.1.17' identified by' 123456'

3 lock the table and export the data in the main database

Flush table with read lock

Show master status; to view the location status of bin-log

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000009 | 264 | |

+-+

/ usr/local/mysql/bin/mysqldump-uroot-plampol-S / tmp/mysql3306.sock-- events-A-B > / tmp/master.sql

4 import data from the database to the master database

Source / tmp/master.sql

5 follow the library execution

Change master to master_host='192.168.1.17'

Master_port=3306

Master_user='repl'

Master_password='123456'

Master_log_file='mysql-bin.000009'

Master_log_pos=264

6 turn on the synchronization switch start slave from the library

Show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.17

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000009

Read_Master_Log_Pos: 500

Relay_Log_File: bogon-relay-bin.000002

Relay_Log_Pos: 489

Relay_Master_Log_File: mysql-bin.000009

Slave_IO_Running: Yes

Slave_SQL_Running: Yes / / two yes ok master-slave configuration is successful

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

Relay_Log_Space: 645

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:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

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.