Mysql master-slave synchronization configuration and mysql master-slave Synchronization

Mysql master-slave synchronization configuration and mysql master-slave Synchronization

1. Install mysql on two Ubuntu machines
1. Check whether mysql is installed in the system.

This is already installed

Run the preceding command if no installation is available.
====================================
Some simple MySQL management:
Start MySQL service: sudo start mysql
Stop MySQL service: sudo stop mysql
Modify the MySQL administrator password: sudo mysqladmin-u root password newpassword

Ii. configuration on the Master server (103.251.237.42)
1. edit my. cnf (command to find the file location: find/-name my. cnf)

Comment out bind-address = 127.0.0.1 in [mysqld], or mysql cannot remotely

In server-id = 1, 1 can be defined by itself, but it must be unique and is the unique identifier of the server.

Log_bin start MySQL binary log
Binlog_do_db specifies the database that records binary logs.
Binlog_ignore_db specifies a database that does not record binary logs.

Comment out binlog_do_db and binlog_ignore_db, indicating that all databases are backed up.

After that, restart the database.
2. log onto the master server mysql to create the account and permissions used by the slave server;

@ Then the IP address can access the master server. The value here is the IP address of the slave server.
Create a masterbackup user whose password is masterbackup and grant the replication slave permission

You can see that masterbackup has been added.

3. view the status of the primary database

Record the mysql-bin.000007 and 276, write the following command to be used;
Change master to master_host = '192. 251.237.42 ', master_port = 103, master_user = 'masterbackup', master_password = 'masterbackup ', master_log_file = 'mysql-bin.000007', master_log_pos = 3306;

Iii. Server Load balancer configuration (103.251.237.45)
1. edit my. cnf (command to find the file location: find/-name my. cnf)

In [mysqld]

Relay-log = slave-relay-bin
Relay-log-index = slave-relay-bin.index
I am not sure what this is. Add the two.

Restart mysql Service

Log on to mysql and stop the synchronization command.

Run the command prepared above. log on to the Slave server and connect to the Master server:

Restart data synchronization;

View Slave information. If both statements are yes, the status is normal.

Iv. Test results from Master/Slave servers

Create a database on the master server

View the created database on the server

Check that the Master/Slave server configuration is complete.
========================================================== ========================================================== ========================

Because the above is the mysql master-slave configuration made by two new machines, you do not need to import the data from the master server to the slave server. If the master server already has some data, you need to add operations to import data from the master server to the slave server;

After configuring the master server to grant permissions to the slave server;
1. Execute the following command to lock the database to prevent data writing;

This command is global read lock. After the command is executed, all tables in all databases will be locked as read-only. Generally, it is used for online database backup. At this time, the database write operation will be blocked and the read operation can proceed smoothly;

2. Exit the mysql command line, export the database to the root file, and name it all. SQL;

3. Use the scp command to transmit the database file all. SQL to the slave server;

4. Connect to the database again and enter the mysql command to view the master Status, and record the values of file and position;

5. Unlock the data table;

Before configuring the slave server, import data first;

1. Import the database of the master server;

2. Subsequent configurations, such as the previous slave server configuration;