MySQL master-slave replication, mysql master-slave

MySQL master-slave replication, mysql master-slave
 Principle:Use mysql binary log files for Data SynchronizationSteps: 1. Configure the Master database (Master) 1. modify the configuration file my. cnf of the master database and add the following Configuration:[Root @ localhost ~] # Nano-w/etc/my. cnf [mysqld] log-bin = mysql-bin server-id = 12. Restart mysql to make the configuration take effect.[Root @ localhost ~] # Service mysqld restart3. log on to mysql as root, create a master-slave replication user, and authorize master-slave Replication[Root @ localhost ~] # Mysql-uroot-p mysql> CREATE USER'Repl'@' % 'Identified'Repl_ Password'; Mysql> grant replication slave on *. *'Repl'@' % '; Mysql> flush privileges;4. view the binary log coordinates of the master database (remember the values of the File and Position columns)Mysql> show master status;

+------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000001 | 747      |              |                  |+------------------+----------+--------------+------------------+

 

Ii. Configure Slave database (Slave)  1. modify the configuration file my. cnf from the slave database and add the following Configuration:[Root @ localhost ~] # Nano-w/etc/my. cnf [mysqld] server-id = 2 2. Modify the MySQL server UUID (Optional). This step is required if the VM is cloned directly)Mv/var/lib/mysql/auto. cnf/var/lib/mysql/auto. cnf. bak 3. Restart mysql to make the configuration take effect and generate a new MySQL server UUID.[Root @ localhost ~] # Service mysqld restart 4. log on to mysql as the root user and set the slave database to access the binary log file of the master database.[Root @ localhost ~] # Mysql-uroot-p

mysql> CHANGE MASTER TO    ->    MASTER_HOST='master_host_name',    ->    MASTER_USER='repl',    ->    MASTER_PASSWORD='repl_password',    ->    MASTER_LOG_FILE='mysql-bin.000001',    ->    MASTER_LOG_POS=747;

  5. Start SlaveMysql> start slave; 6. Check the Slave connection status. (If Yes is set for Slave_IO_Running and Slave_ SQL _Running, the connection is successful)Mysql> show slave status \ G; ***************************** 1. row ************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.168.110 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 747 Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 320 runtime: mysql-bin.000001 runtime: Yes Slave_ SQL _Running: Yes