MySQL master-slave synchronization setup

MySQL master-slave synchronization build master database: IP: 192.168.18.204 edit etcmy. cnf file open log-binvimetcmy.cnflog-binapplicationmysqld

MySQL master-slave synchronization build master database: IP: 192.168.18.204 edit/etc/my. cnf file open log-bin vim/etc/my. cnf log-bin =/application/mysql/d

MySQL master-slave synchronization setup

Master database:

IP: 192.168.18.204

Edit/etc/my. cnf file to open log-bin

Vim/etc/my. cnf

Log-bin =/application/mysql/data/mysql. bin

Server-id = 1

View:

Log_bin should be in the on status

[Root @ mysql-master-w ~] # Mysql-uroot-p123456-e "show variables like 'Log _ bin ';"

+ --------------- + ------- +

| Variable_name | Value |

+ --------------- + ------- +

| Log_bin | ON |

[Root @ mysql-master-w ~] # Mysql-uroot-p123456-e "show variables like 'server _ id ';"

+ --------------- + ------- +

| Variable_name | Value |

+ --------------- + ------- +

| Server_id | 1 |

+ --------------- + ------- +

[Root @ mysql-master-w ~] #

Authorize a user to be synchronized and log on to mysql:

Grant replication slave on *. * to 'rep '@ '192. 192. %' identified by '123 ';

# User rep, on all computers on 192.168.18.0/24, the password is 123456

Select user, host from mysql. user; # view the user, and ensure that the user is correctly added.

Lock table:

Flushtables with read lock; # log on to mysql

Export data:

Mysqldump-uroot-poldboy123-B-A -- events | gzip>/opt/new. SQL .gz

Push new. SQL .gz to the slave database server.

Unlock:

Unlock tables; # mysql operations

Mysql> show master status; ### write down the file name and location information

+ ------------------ + ---------- + -------------- + ------------------ +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ ------------------ + ---------- + -------------- + ------------------ +

| Mysql-bin.000002 | 412 |

+ ------------------ + ---------- + -------------- + ------------------ +

Slave database:

Server ip: 192.168.18.205

Modify server-id = 2 in/etc/my. cnf. Do not use the same id as that in the master database.

Import the data new. SQL .gz of the master database to the database:

Gzip-d new. SQL .gz

Mysql-uroot-p123456

Execute the following content in the slave database:

CHANGE MASTER

MASTER_HOST = '1970. 168.18.204 ',

MASTER_PORT = 3306,

MASTER_USER = 'rep ',

MASTER_PASSWORD = '000000 ',

MASTER_LOG_FILE = 'mysql-bin.000002 ', #### the content here is the same as the master database show master status;

MASTER_LOG_POS = 412; # Position information of the master database show master status;

###############################

OK. The above content will be written to the master.info file.

Run the following command in the database:

Slave start; # enable synchronization

Show slave status \ G # Check whether the database is synchronized

If the information contains the following two yes values, one 0, indicating success.

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

Seconds_Behind_Master: 0

The above is the whole process of master-slave synchronization.

You can create or update databases or tables in the master database to check whether there are any changes in the database for testing purposes.

Recommended reading:

Load Nginx in Ubuntu for high-performance WEB Server 5 --- MySQL master/Master Synchronization

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL Master/Slave failure error Got fatal error 1236

MySQL master-slave replication, implemented on a single server