Detailed steps to add a new database to the MySQL master-slave replication list

Environmental preparation

Master server (Master)

IP: 192.168.1.100
MySQL version: 5.7.31

From the server (Slave)

IP: 192.168.1.101
MySQL version: 5.7.31

Ensure that the time of the master and slave server is synchronized and that the MySQL version is consistent to avoid replication problems caused by inconsistent versions.

Step 1: Create a new database on the main server

First, log in to the MySQL console on the main server:

mysql -u root -p

Create a new database:

CREATE DATABASE new_database;

Step 2: Set the replication permissions of the main server

Make sure the slave server has permission to access the new database on the master server. If the replication user has not been set up yet, you can create one:

CREATE USER 'repl'@'192.168.1.101' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.101';
FLUSH PRIVILEGES;

Step 3: Lock the main database and get the binary log location

To ensure data consistency, the primary database needs to be temporarily locked when adding a new database:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Record itFileandPosition, which information will be used when configuring replication from the server.

Step 4: Export the data from the new database

UsemysqldumpTools export data from a new database:

mysqldump -u root -p --databases new_database > /path/to/backup/new_database.sql

Step 5: Unlock the main database

After completing the data export, unlock the main database:

UNLOCK TABLES;

Step 6: Import data from the server

Transfer the exported data file to the slave server and import it into MySQL:

scp /path/to/backup/new_database.sql [email protected]:/path/to/backup/

mysql -u root -p

Create a database with the same name and import the data:

CREATE DATABASE new_database;
SOURCE /path/to/backup/new_database.sql;

Step 7: Configure the replication from the server

Edit the MySQL configuration file from the server (usually located at/etc/mysql/or/etc/), make sure the following parameters are configured correctly:

[mysqld]
server-id=2
relay-log=mysql-relay-bin
log_bin=mysql-bin
binlog_do_db=new_database

Restart MySQL service to apply changes:

sudo systemctl restart mysql

Step 8: Start replication from the server

CHANGE MASTER TO
MASTER_HOST='192.168.1.100',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1234;
 
START SLAVE;

Among them,MASTER_LOG_FILEandMASTER_LOG_POSThe value of is the binary log file name and location recorded in step 3.

Step 9: Verify the replication status

Check the replication status from the server to make sure everything is OK:

SHOW SLAVE STATUS\G

Focus on the following fields:

Slave_IO_Running: It should be displayed asYes
Slave_SQL_Running: It should be displayed asYes
Last_Error: It should be empty

Through the above steps, you can successfully add a new database in an existing MySQL master-slave replication environment. Make sure to operate every step carefully and to view logs and error messages in time when problems occur. This article details how to add a new database to the MySQL master-slave replication environment, including key steps such as environment preparation, database creation, permission settings, data export and import, and replication configuration. Hope it will be helpful to readers in actual operation. In MySQL's master-slave replication configuration, adding a new database to an existing master-slave replication list is a common requirement. This usually involves several steps: creating a database on the master server, ensuring that the data for that database is correctly copied to the slave server, and verifying that the replication is successful. Here is a specific example showing how to implement this process.

Prerequisites

There is already a MySQL master-slave replication environment: Suppose you have set up a MySQL master-slave replication environment, including a master server (Master) and at least one slave server (Slave).
MySQL access permissions: You need to have proper access to the master and slave servers in order to execute SQL commands.

Step 1: Create a database on the main server

First, log in to your MySQL master server and create a new database. For example, create a name callednew_databaseDatabase:

CREATE DATABASE new_database;

Step 2: Make sure the data is copied to the server

By default, if the master-slave replication is configured correctly, the newly created database will be automatically replicated to all slaves. However, to ensure that you can check the replication configuration file of the primary server (usually or) to make sure that the new database is not excluded. The relevant parts in the configuration file might look like this:

[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=new_database

binlog-do-dbSpecifies the database to record binary logs. If you want to copy all databases, you can omit this line.
If your configuration is usedbinlog-ignore-dbTo exclude certain databases, you need to make sure that the new database is not in the exclusion list.

Step 3: Check the database from the server

SHOW DATABASES;

You should be able to see in the listed databasenew_database. If you can't see it, you can try to start the replication process manually:

START SLAVE;

Then check the database list again.

Step 4: Verify data copying

To further verify that the data is being copied from the master to the slave, some test data can be added to the new database on the master:

USE new_database;
CREATE TABLE test_table (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));
INSERT INTO test_table (name) VALUES ('Test Entry');

Then, query the same table on the server to confirm that the data has been copied:

USE new_database;
SELECT * FROM test_table;

If everything works fine, you should be able to see the same data inserted on the main server.

Things to note

Backup: It is recommended to back up important data before making any changes.
monitor: In production environments, it is recommended to continuously monitor the status of master-slave replication to ensure that there are no delays or errors occur.
Permission Management: Ensure that the user has sufficient permissions to perform the above operations.

Through the above steps, you can successfully add a new database to your existing MySQL master-slave replication environment. Setting up Master-Slave Replication in MySQL is a common operation to improve data reliability and availability. When it is necessary to add a new database to an existing master-slave replication configuration, it is often necessary to ensure that this new database can be properly created and synchronized to the slave server on the master. The following steps will explain in detail how to complete this process:

1. Create a new database on the main server

First, log in to MySQL on the main server and create a new database.

mysql -u root -p

After entering the password, execute the following SQL command to create a new database:

CREATE DATABASE new_database;

2. Configure the replication options for the primary server

Make sure that the master server has Binary Logging configured, as this is the basis for replication. Check or modify(orOn Windows system) File, make sure to include the following configuration:

[mysqld]
server-id=1
log-bin=mysql-bin
binlog-format=mixed

Restart MySQL service to apply changes:

sudo systemctl restart mysql

3. Create a user for replication

If you have not created a user for replication, you can create one on the main server:

CREATE USER 'replication_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
FLUSH PRIVILEGES;

4. Get the status of the main server

Execute the following command on the main server to get the current binary log location:

SHOW MASTER STATUS;

Note it downFileandPositionThe value of the field, which will be used in the server configuration.

5. Configure replication on the server

Log in to MySQL on the slave server and use the following command to set up the slave server to connect to the master server:

CHANGE MASTER TO
MASTER_HOST='master_server_ip',
MASTER_USER='replication_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='recorded_log_file_name',
MASTER_LOG_POS=recorded_log_position;

Replace the above commandmaster_server_ip、recorded_log_file_nameandrecorded_log_positionAs the actual value obtained from the master server.

6. Start the replication process from the server

Start the replication process from the server:

START SLAVE;

7. Check the replication status

Check the replication status from the server to make sure everything is working:

SHOW SLAVE STATUS\G

Check whether both the Slave_IO_Running and Slave_SQL_Running fields in the output are displayed as Yes, which indicates that the replication is working properly.

8. Create the same database on the server

To ensure data consistency, you may also need to manually create the same new database on the slave server, or have the replication process handle this automatically. If you choose to create manually, you can do:

CREATE DATABASE new_database;

Things to note

Ensure the network connection between the master and slave servers is stable.
Check the replication status regularly to ensure there are no delays or errors.
If you encounter replication problems, you can try to stop the replication process from the server and reconfigure itCHANGE MASTER TOCommand, and then start copy again.

Through the above steps, you should be able to successfully add a new database to the Master-Slave Replication Configuration of MySQL.

The above is the detailed steps for adding a new database to the MySQL master-slave replication list. For more information about adding a new database to the MySQL master-slave replication list, please follow my other related articles!