Ubuntu MySQL master-slave Database Hot standby experiment

0 Installing the Database

sudo apt-get install Mysql-server

Set Root password

1 log in to MySQL database create user

Etc/init.d/mysql start

mysql-uroot-p password;

Insert into Mysql.user (Host,user,password) VALUES ("%", "DBA", Password ("123456"));

Grant all privileges on * * to ' DBA ' @ ' percent ' identified by ' 123456 ';

SELECT DISTINCT CONCAT (' User: ', user, ' @ ', host, '; ') as query from Mysql.user;

Flush privileges;

Configuring the master server my.conf file

Find [mysqld] Settings

Server-id = 1 ID settings cannot be the same.
Log_bin =/var/log/mysql/mysql-bin.log
Expire_logs_days = 10
Max_binlog_size = 100M
#binlog_do_db = Include_database_name
binlog_do_db =hnswls The name of the database that needs to be synchronized

Log-slave-updates ' This parameter must be added, otherwise it will not add the updated record to the binary file

Slave-skip-errors ' Skip error, resume copy operation

#binlog_ignore_db = Include_database_name database name that does not need to be synchronized

Modify the my.conf file from the server

Server-id =2

Log_bin=/var/log/mysql/mysql-bin.log

Replicate-do-db=hnswls

Log-slave-updates

master-host=192.168.101.171 Specify the IP of the primary server

MASTER-USER=DBA Primary Server authorized user

master-password=123456 Password

master-port=3306 Port

master-connect-retry=30 If a primary server is found to be disconnected from the server, the time difference (in seconds) to reconnect

replicate-ignore-db= database names that do not need to be synchronized

To re-synchronize from the server to specify a new location

Sometimes the master-slave synchronization has a problem, you need to synchronize from the next location of the log position, the equivalent of skipping that error, this time can also use the Change Master command to handle, as long as the corresponding log location can be found, such as:

Change MASTER to master_host= ' IP address ', master_user= ' username ', master_password= ' 123456 ', master_log_file= ' mysql-bin.000006 ' , master_log_pos=106;

Restart from server database

Start the slave_io_running slave_sql_running process

Here are some commands to manage MySQL master-slave synchronization:

1. Stop MySQL Sync

STOP SLAVE Io_thread; #停止IO进程

STOP SLAVE Sql_thread; #停止SQL进程

STOP SLAVE; #停止IO和SQL进程

2. Start MySQL Sync

START SLAVE Io_thread; #启动IO进程

START SLAVE Sql_thread; #启动SQL进程

START SLAVE; #启动IO和SQL进程

3. Reset MySQL Sync

RESET SLAVE;

View MySQL sync status show SLAVE status;

See

Slave_io_running:yes

Slave_sql_running:yes

The configuration succeeds

Test whether the master-slave server can synchronize

To create a new table above the primary server, you must hnswls Data under

Mysql> Use REPL

Database changed

Mysql> CREATE TABLE Test (ID int,name char (10));

Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT INTO test values (1, ' Zaq ');

Query OK, 1 row Affected (0.00 sec)

mysql> INSERT INTO test values (1, ' xsw ');

Query OK, 1 row Affected (0.00 sec)

Mysql> select * from test;

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

| ID | name |

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

| 1 | Zaq |

| 1 | xsw |

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

2 rows in Set (0.00 sec)

Check to see if it's synced from the server.

mysql> use hnswls;

Database changed

Mysql> select * from test;

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

| ID | name |

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

| 1 | zaq |

| 1 | xsw |

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

2 rows in Set (0.00 sec)

Ubuntu MySQL master-slave Database Hot standby experiment