Centos's Mysql's master from synchronization configuration
Environment:
System: CentOS Linux release 6.0.x86_64
Software: mysql.5.1.X
Master IP 192.168.200.1
Slave IP 192.168.200.2
mysql installation please refer to mysql single instance installation
The role of MYSQL master-slave synchronization
(1) Data distribution
(2) Load balancing
(3) Backup
(4) High availability and fault tolerance
The principle of MYSQL master-slave synchronization
About the master-slave synchronization of MYSQL, the most important thing is to understand how the master-slave synchronization of MYSQL works, that is, the principle of master-slave synchronization.The following figure can clearly guide the process of its work:
Configuration:
One, Master configuration
1.Add the following to/etc/my.cnf:
server-id=1 sync_binlog=1 auto_increment_increment=2 auto_increment_offset=1 log-slave-updates log-bin=mysql-bin binlog-ignore-db=mysql character-set-server=utf8 init_connect='SET NAMES utf8'
The default character set is utf8, you can choose this configuration according to the actual situation.
2.Create a synchronization account on Mster
GRANT REPLICATION SLAVE,FILE ON *.* TO 'backup'@'192.168.200.2' IDENTIFIED BY '123456'; FLUSH PRIVILEGES;
Second, Slave configuration, add the following content in/etc/my.cnf:
server-id=2 sync_binlog=1 auto_increment_increment=1 auto_increment_offset=1 log-slave-updates log-bin=mysql-bin binlog-ignore-db=mysql master-host=192.168.200.2 master-user=backup master-password=123456 master-port=3306
Three, restart the Master and slave server mysql services.
Fourth, the slave server reads data from the master server and starts synchronization
1.
FLUSH TABLES WITH READ LOCK; #Execute this first to lock the main server LOAD DATA FROM MASTER # statement, you must grant global FILE and SELECT permissions, only for MyISAM engine, useless for InnoDB tables.
2.If the data is InnoDB, the table structure and data need to be exported separately on the server, and then copied to the Slave server.Export the entire database structure and data: mysqldump-u username-p password-h host database > filename.sql Export the data and structure of a table: mysqldump-u username-p password-h host database table > filename.sql only exports the data of the table in the database: mysqldump-u username-p password-h host-T database table > filename.sql only exports the structure of the table in the database: mysqldump-u username-p password-h host-d database table > filename.sql
3.Check the status of the master on the Master server
mysql> show master status; +-------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------+----------+--------------+------------------+ | mysql-bin.000011 | 106 | db_cngamer | mysql | +-------+----------+--------------+------------------+ 1 row in set (0.00 sec)
You can see that the log file is: mysql-bin.000011, the synchronization point is 106
4.Execute the following statement on the Slave server
mysql> CHANGE MASTER TO MASTER_HOST='192.168.11.244',MASTER_USER='backup',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000011',MASTER_LOG_POS=106; Query OK, 0 rows affected (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec)
4.Check the synchronization status on the slave
mysql> show slave statusG **************************** 1.row ******************** ****** Slave_IO_State: Queueing master event to the relay log Master_Host: 192.168.200.1 Master_User: backup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000011 Read_Master_Log_Pos: 106 Relay_Log_File: mysqld-relay-bin.000010 Relay_Log_Pos: 106 Relay_Master_Log_File: mysql-bin.000011 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: typecho Replicate_Ignore_DB: mysql,test 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: 106 Relay_Log_Space: 106 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 1 row in set (0.00 sec)
You can see that the values of the Slave_IO_Running and Slave_SQL_Running columns are both "Yes", which indicates that the Slave's I/O and SQL threads are running normally.
5.Configuration completed
0 Comments