• notice
  • Congratulations on the launch of the Sought Tech site

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

Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+