MySQL 5.5 master-slave synchronization settings tutorial, mysql5.5 master-slave tutorial

MySQL 5.5 master-slave synchronization settings tutorial, mysql5.5 master-slave tutorial

First modify my. cnf Configuration Add the following content in [mysqld] log-bin = mysql-binlog-bin-index = mysql-bin.indexserver-id = 1sync_binlog = 1binlog_format = mixed and specify the database to be synchronized, and ignore the database binlog-do-db = testdbbinlog-ignore-db = mysqlbinlog-ignore-db = performance_schemabinlog-ignore-db = information_schema in [mysqldump] to modify the content to max_allowed_packet = 32M note: make sure that max_allowed_packet has a relatively large value. For example, max_allowed_packet = m to restart mysq. Lservice mysql restart creates a replication user create user funsion ON the Master; grant replication slave on *. * TO funsion identified by 'ifunsion. com '; # in the test environment, the password is xyzzy + ---------------------------------------------------- +, modify the configuration of Slave (10.1.123.160), and modify my. cnf added the following content in [mysqld] server-id = 2log-bin = mysql-binrelay-log-index = slave-relay-bin.indexrelay-log = slave-relay-binsync_master_info = 1sync_relay_log = 1sync_r Elay_log_info = 1 # the above three lines only use MySQL 5.5 TO input mysql-u root-p TO enter the mysql command line, and enter change master to MASTER_HOST = '10. 1.123.233 ', MASTER_USER = 'funsion', MASTER_PORT = 3306, MASTER_PASSWORD = 'ifunsion. com '; change master to MASTER_CONNECT_RETRY = 30; enter start slave; + ------------------------- if an error occurs, it cannot be started ----------------- + enter the Master database, enter show master status \ G to record the currently used log-binfile, and position (for example, the current log is a mysql-bin.000001, The location is 107) then enter the Slave database and enter stop slave; change master to master_log_file = 'mysql-bin.000001 ', master_log_pos = 107; then enter start slave; finally, run yum-y install ntpdatentpdate cn on the server. pool. ntp. orgclock-w synchronizes the time of several servers + ---------------------------- some possible commands --------------------------- + execute the lock table: flush tables with read lock; the purpose of this step is to ensure that no new data exists in the master database during the process of creating the master and slave databases; otherwise, the synchronization settings will be troublesome for the master database to unlock: unlock tables; the reset master command deletes all binary log files and clears binary logs. Base log index file. The reset slave command deletes all files used for Slave replication and starts again. + ----------------------- Other reference documents (not verified) -------------------------- + create an image for the slave database when the database service cannot be stopped: Case: Server 1: master server 2: from now on, due to load problems, the server must be mounted to the slave server whose number 3 is also number 1. However, database 1 cannot be stopped, database 2 cannot be stopped, and master-slave synchronization processes cannot be stopped (demanding ). You can do this: In master: mysqldump-uroot-pxxxx-h127.0.0.1 -- databases db1 db2 db3 -- master-data = 1> bak. sql3 server slave stop; then bak. SQL imports Server 3 slave start; Server 3 automatically updates the node at the moment of export. Because the -- master-data = 1 parameter is added with the change statement at the bottom after the SQL file is exported. If -- master-data = 0, it is not included. It is very convenient, but it is only suitable for the case where the library is not too large. In this case, the exported database has a total of 6 GB.

 

How to synchronize existing data in mysql master-slave synchronization,

-- The host opens two windows, one for mysql and the other for shell.
-- Host blocking write operations
Mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
Mysql>
Mysql>
Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000002 | 1529881 | openser | mysql, test |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)
-- Export the host database in another window
Mysqldump-u root-p123456 -- opt-R openser> openser20121203. SQL
-- Unlock the window host just now
Mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
Mysql>
-- Package data files to the slave machine
Drop database openser;
Create database openser;
Mysql-u root-p123456 openser <openser20121127. SQL
-- Slave operation
Slave stop;
Reset slave;
Change master to MASTER_HOST = '192. 168.21.26 ',
MASTER_USER = 'repl _ user ',
MASTER_PASSWORD = '000000 ',
MASTER_LOG_FILE = 'mysql-bin.000002 ',
MASTER_LOG_POS = 1529881;
Start slave;
Show slave status \ G

Windows mysql master-slave Synchronization

How long will it take for synchronization to start? Which running Thread Programming no cannot be synchronized? Is there any update in the err log of the database ?? What is an update error message?

Add one by one