MariaDB single-host dual-instance master-slave Replication

MariaDB single-host dual-instance master-slave Replication

MariaDB single-host dual-instance master-slave Replication

MariaDB is actually the same as MySQL, but its name is different.

Start ~ In the previous experiment, we installed MariaDB on a single machine with multiple instances.

Dual-instance port 3307 master database, 3308 slave Database

3307 configure mysqld for the master instance

[Mysqld2]
Port = 3307
Socket =/tmp/mysql3307.sock
Pid-file =/tmp/mysql3307.pid
Max_allowed_packet = 1 M
Net_buffer_length = 2 k
Table_open_cache = 4
Sort_buffer_size = 64 k
Thread_stack = 128 k
Basedir =/usr/local/mysql
Datadir =/data/mydata2
Servers-id = 244
Log-bin = master3307-bin
Log-bin-index = master3307-bin.index
Thread_concurrency = 4
Innodb_file_per_table = 1
# Mysql_pwd = 'teleframework'

3308 configure mysqld from an instance

[Mysqld3]
Port = 3308
Socket =/tmp/mysql3308.sock
Pid-file =/tmp/mysql3308.pid
Max_allowed_packet = 1 M
Net_buffer_length = 2 k
Table_open_cache = 4
Sort_buffer_size = 64 k
Thread_stack = 128 k
Basedir =/usr/local/mysql
Datadir =/data/mydata3
Innodb_file_per_table = 1
Servers-id = 245
Log-bin = mysql3308-bin
Relay-log = relay-log
Relay-log-index = relay-log-index
Thread_concurrency = 4
Read-only = on

Start Master/Slave Configuration

Step 1. Connect to the master database and authorize a replication slave account, and refresh the authorization table

[Root @ e3 mysql] # mysql-uroot-h127.0.0.1-P3307-p
Enter password:
Welcome to the MariaDB monitor. Commands end with; or \ g.
Your MariaDB connection id is 8
Server version: 10.0.15-MariaDB-log Source distribution
 
Copyright (c) 2000,201 4, Oracle, SkySQL AB and others.
 
Type 'help; 'or' \ H' for help. type' \ C' to clear the current input statement.
 
MariaDB [(none)]> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mysql |
| Performance_schema |
| Test |
| Test1 |
+ -------------------- +
5 rows in set (0.00 sec)
 
MariaDB [(none)]> grant replication slave on *. * to 'repl' @ '192. 192.% 'identified by 'repl ';
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> show master status;
+ ----------------------- + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ----------------------- + ---------- + -------------- + ------------------ +
| Master3307-bin.000005 | 632 |
+ ----------------------- + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)
 
MariaDB [(none)]>

2. Connect to the slave database and specify the master database to start the slave

[Root @ e3 ~] # Mysql-uroot-p 'telframework'-h127.0.0.1-P3308
Welcome to the MariaDB monitor. Commands end with; or \ g.
Your MariaDB connection id is 3
Server version: 10.0.15-MariaDB-log Source distribution
 
Copyright (c) 2000,201 4, Oracle, SkySQL AB and others.
 
Type 'help; 'or' \ H' for help. type' \ C' to clear the current input statement.
 
MariaDB [(none)]> change master to master_host = '2017. 168.0.244 ', master_user = 'repl', master_password = 'repl', master_port = 3307, master_log_file = 'master3307-bin.000005', master_log_pos = 632;
ERROR 1201 (HY000): cocould not initialize master info structure for ''; more error messages can be found in the MariaDB error log # I reported this ERROR, if you report an error like me, run the following command:
MariaDB [(none)]> reset slave;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> change master to master_host = '1970. 168.0.244 ', master_user = 'repl', master_password = 'repl', master_port = 192 ,;
Query OK, 0 rows affected (0.22 sec) # Now master_log_file = 'master3307-bin.000005 'is connected ', master_log_pos = 632 indicates that the replication starts from the binary log file on the master server because this is the copy authorization information, so there is no need to copy it. You can view the show master status on the master server; view the show binglog events in 'master-bin.000001 ';
MariaDB [(none)]> show slave status \ G
* *************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.0.244
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: master3307-bin.000005
Read_Master_Log_Pos: 632
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 4
Relay_Master_Log_File: master3307-bin.000005
Slave_IO_Running: No # Whether the slave server works normally depends on whether the value is YES.
Slave_ SQL _Running: No #
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 632
Relay_Log_Space: 248
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_ SQL _Errno: 0
Last_ SQL _Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
1 row in set (0.00 sec)
MariaDB [(none)]> start slave; # enable slave
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> show slave status \ G
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.244
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: master3307-bin.000005
Read_Master_Log_Pos: 632
Relay_Log_File: relay-log.000003
Relay_Log_Pos: 540
Relay_Master_Log_File: master3307-bin.000005
Slave_IO_Running: Yes # indicates that the slave server is working normally.
Slave_ SQL _Running: Yes #
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 632
Relay_Log_Space: 831
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_ SQL _Errno: 0
Last_ SQL _Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 244
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:

To test!

Create a database on the master to check whether the slave database can be copied

Create on Master

MariaDB [(none)]> create database kcwtest;
Query OK, 1 row affected (0.00 sec)
 
MariaDB [(none)]> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Kcwtest |
| Mysql |
| Performance_schema |
| Test |
| Test1 |
+ -------------------- +
6 rows in set (0.00 sec)

View from above

MariaDB [(none)]> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Kcwtest |
| Mysql |
| Performance_schema |
| Test |
+ -------------------- +
5 rows in set (0.00 sec)

You can copy it! In this way, single-host instance replication is realized. The three instances I have created, port 3306 is used by default, and port 3307 and port 3308 are copied from the master.

Install LAMP (Apache with MariaDB and PHP) in CentOS/RHEL/Scientific Linux 6)

Implementation of MariaDB Proxy read/write splitting

How to compile and install the MariaDB database in Linux

Install MariaDB database using yum in CentOS

Install MariaDB and MySQL

How to migrate MySQL 5.5 database to MariaDB 10 on Ubuntu

Install MariaDB on the Ubuntu 14.04 (Trusty) Server