MARIADB single-Machine dual-instance master-slave replication

Mariadb in fact, like MySQL, there is nothing different that is the name is not the same ~.~!

Start ~ Previous experiment is a MARIADB single-machine multi-instance installation

Dual instance Port 3307 Main Library, 3308 from library

3307 mysqld configuration of the primary instance

[Mysqld2]port=3307socket=/tmp/mysql3307.sockpid-file=/tmp/mysql3307.pidmax_allowed_packet=1mnet_buffer_length= 2ktable_open_cache=4sort_buffer_size=64kthread_stack=128kbasedir=/usr/local/mysqldatadir=/data/ Mydata2server-id=244log-bin = Master3307-binlog-bin-index = Master3307-bin.indexthread_concurrency=4innodb_file_ per_table = 1#mysql_pwd= ' Teleframe '

3308 mysqld configuration from an instance

[Mysqld3]port=3308socket=/tmp/mysql3308.sockpid-file=/tmp/mysql3308.pidmax_allowed_packet=1mnet_buffer_length= 2ktable_open_cache=4sort_buffer_size=64kthread_stack=128kbasedir=/usr/local/mysqldatadir=/data/mydata3innodb_ file_per_table = 1server-id=245log-bin=mysql3308-binrelay-log = Relay-logrelay-log-index = Relay-log-indexthread_ Concurrency=4read-only = On

Start master/slave configuration

Step 1, connect to the main library to authorize an REPLICATION SLAVE account, and refresh the authorization form

[[email protected] mysql]# mysql -uroot -h127.0.0.1 -p3307 -penter  Password: welcome to the mariadb monitor.  commands end with  ; or \g.Your MariaDB connection id is 8Server version:  10.0.15-mariadb-log source distributioncopyright  (c)  2000, 2014, 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.168.0.% '  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 |   &Nbsp;  632 |               |                  | +-----------------------+----------+--------------+------------------+1 row in set  (0.00  SEC) mariadb [(none)]>

        2, connecting to the specified main library from the library, starting slave

[[email protected] ~]# mysql -uroot -p ' Teleframe '  -h127.0.0.1 -p3308welcome  to the mariadb monitor.  commands end with ; or \g. your mariadb connection id is 3server version: 10.0.15-mariadb-log  source distributioncopyright  (c)  2000, 2014, 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= ' 192.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): could not initialize master info structure  for  ';  more error messages can be found in the  mariadb error log  #我这报错了, if you give me the same error, execute the following command mariadb [(none)]> reset slave; query ok, 0 rows affected  (0.00 sec) mariadb [(none)]> change  Master to master_host= ' 192.168.0.244 ', master_user= ' repl ', master_password= ' repl ', master_port=3307,; query ok, 0 rows affected  (0.22 sec)   #好了现在连接上了        master_log_file= ' master3307-bin.000005 ',master_log_pos=632   Indicates that the binary log file from the primary server starts copying because this is the replication authorization information, so there is no need to copy, on the primary server can view show master status;  view content 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  #    There is no normal work from the server mainly to see if this 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: nullmaster_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;   #好启动slaveQuery  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   #表示从服务器正常工作了              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: 0Master_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 library on top of the master to see if it can be copied to

Our Lord creates

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 top

MariaDB [(None)]> show databases;+--------------------+| Database |+--------------------+| Information_schema | | Kcwtest | | MySQL | | Performance_schema | | Test |+--------------------+5 rows in Set (0.00 sec)

Can copy! This implementation of single-machine instance replication, I do 3 instances, 3306 port to the default, 3307,3308 port master from replication.

MARIADB single-Machine dual-instance master-slave replication