Rumah > pangkalan data > tutorial mysql > Multi-Master-Rep in MySQL DB_MySQL

Multi-Master-Rep in MySQL DB_MySQL

WBOY
Lepaskan: 2016-06-01 13:14:32
asal
1051 orang telah melayarinya

Multi-master-rep

two masters:-- 192.168.126.128 mysql 5.6.12-- 192.168.126.129 mysql 5.6.12
Salin selepas log masuk
slave :-- 192.168.126.130 MariaDB 10.0
Salin selepas log masuk
from one master machine create new db and insert one record:23:44:55 (none)> show master status	-> ;+------------------+----------+--------------+------------------+-------------------+| File			 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000004 |	544 |			|				|				 |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.07 sec)23:45:51 (none)> create database mariatest1;Query OK, 1 row affected (0.21 sec)23:50:14 (none)> use mariatest1;Database changed23:52:16 mariatest1> create table liuyang (id bigint);Query OK, 0 rows affected (1.42 sec)23:52:30 mariatest1> 23:52:31 mariatest1> insert into liuyang values (1);Query OK, 1 row affected (0.17 sec)23:52:37 mariatest1> commit;Query OK, 0 rows affected (0.01 sec)
Salin selepas log masuk
from secoend master machinecreate new db and insert one record:23:47:23 (none)> show master status/G*************************** 1. row ***************************	File: mysql-bin.000005 Position: 550 Binlog_Do_DB:Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)23:47:29 (none)> create database mariatest2;Query OK, 1 row affected (0.13 sec)23:50:27 (none)> use mariatest2;Database changed23:52:48 mariatest2> create table liuyang (id int);Query OK, 0 rows affected (0.65 sec)23:53:43 mariatest2> insert into liuyang values (1);Query OK, 1 row affected (0.12 sec)23:53:55 mariatest2> commit;Query OK, 0 rows affected (0.00 sec)
Salin selepas log masuk
change Mariadb slave connect to these two masters:MariaDB [(none)]> SET @@default_master_connection='t1';Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> CHANGE MASTER 't1' TO MASTER_HOST = '192.168.126.128', MASTER_USER = 'repluser', MASTER_PASSWORD = 'rep', MASTER_PORT = 3333 ,MASTER_LOG_FILE = 'mysql-bin.000005',MASTER_LOG_POS = 550;Query OK, 0 rows affected (0.01 sec)MariaDB [(none)]> SET @@default_master_connection='t2';Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> CHANGE MASTER 't2' TO MASTER_HOST = '192.168.126.129', MASTER_USER = 'repluser', MASTER_PASSWORD = 'rep', MASTER_PORT = 3333 ,MASTER_LOG_FILE = 'mysql-bin.000004',MASTER_LOG_POS = 544;Query OK, 0 rows affected (0.01 sec)
Salin selepas log masuk
[mysql@mariadb ~]$ mysql--socket=/mysql3333/mysql/data/mysql.sockWelcome to the MariaDB monitor.Commands end with ; or /g.Your MariaDB connection id is 13Server version: 10.0.10-MariaDB-log MariaDB ServerCopyright (c) 2000, 2014, Oracle, SkySQL Ab and others.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.MariaDB [(none)]> START ALL SLAVES;Query OK, 0 rows affected, 1 warning (0.00 sec)MariaDB [(none)]> MariaDB [(none)]> show slave status-> /G;Empty set (0.00 sec)ERROR: No query specifiedMariaDB [(none)]> show slave status /G;Empty set (0.00 sec)ERROR: No query specifiedMariaDB [(none)]> show slave 'T1' status /G;*************************** 1. row ***************************	 Slave_IO_State: Waiting for master to send event		Master_Host: 192.168.126.128		Master_User: repluser		Master_Port: 3333		Connect_Retry: 60	Master_Log_File: mysql-bin.000005	Read_Master_Log_Pos: 550	 Relay_Log_File: GSS-01-relay-bin-t1.000002		Relay_Log_Pos: 407	Relay_Master_Log_File: mysql-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: 550	Relay_Log_Space: 708	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: 6	 Master_SSL_Crl: 	 Master_SSL_Crlpath: 		 Using_Gtid: No		Gtid_IO_Pos: 1 row in set (0.00 sec)ERROR: No query specifiedMariaDB [(none)]> show slave 'T2' status /G;*************************** 1. row ***************************	 Slave_IO_State: Waiting for master to send event		Master_Host: 192.168.126.129		Master_User: repluser		Master_Port: 3333		Connect_Retry: 60	Master_Log_File: mysql-bin.000004	Read_Master_Log_Pos: 544	 Relay_Log_File: GSS-01-relay-bin-t2.000002		Relay_Log_Pos: 407	Relay_Master_Log_File: mysql-bin.000004	 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: 544	Relay_Log_Space: 708	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: 5	 Master_SSL_Crl: 	 Master_SSL_Crlpath: 		 Using_Gtid: No		Gtid_IO_Pos: 1 row in set (0.00 sec)ERROR: No query specifiedMariaDB [(none)]> show databases;+--------------------+| Database	 |+--------------------+| information_schema || mariatest1	 || mariatest2	 || mysql	|| performance_schema || test	 |+--------------------+6 rows in set (0.06 sec)MariaDB [(none)]> MariaDB [(none)]> select * from mariatest1.liuyang;+------+| id |+------+|1 |+------+1 row in set (0.00 sec)MariaDB [(none)]> select * from mariatest2.liuyang;+------+| id |+------+|1 |+------+1 row in set (0.00 sec)
Salin selepas log masuk

REF:https://mariadb.com/kb/en/change-master-to/
https://mariadb.com/kb/en/multi-source-replication/
https://mariadb.com/kb/zh-cn/installing-mariadb-binary-tarballs/

sumber:php.cn
Kenyataan Laman Web ini
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn
Tutorial Popular
Lagi>
Muat turun terkini
Lagi>
kesan web
Kod sumber laman web
Bahan laman web
Templat hujung hadapan