双主复制: 在两台server配置my.cnf [root@localhost mysql]# egrep -v "^$|^#" /etc/my.cnf datadir = /mydata/data skip_name_resolve = ON innodb_file_per_table = ON relay-log = relay-log auto-increment-offset = 1 表示自增长字段从那个数开始,他的取值范围是1 .. 65535 auto-increment-increment = 2 表示自增长字段每次递增的量,其默认值是1,取值范围是1 .. 65535 log-bin=mysql-bin binlog_format=mixed server-id = 1 两台server配置文件几乎一样,只需要指定auto-increment-offset = 1为偶数或者奇数; 在每台server创建可复制的用户和密码如下: 在192.168.8.200创建用户: MariaDB [(none)]> grant replication slave,replication client ON *.* to 'glq'@'192.168.%.%' identified by '123123'; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 245 | | mysql-bin.000002 | 28799 | | mysql-bin.000003 | 1069459 | | mysql-bin.000004 | 500 | +------------------+-----------+ 4 rows in set (0.00 sec) MariaDB [(none)]> change master to master_host='192.168.8.201',master_user='glq1',master_password='123123',master_log_file='mysql-bin.000004',master_log_pos=501; Query OK, 0 rows affected (0.07 sec) MariaDB [(none)]> slave start -> ; Query OK, 0 rows affected (0.07 sec) MariaDB [(none)]> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.8.201 Master_User: glq1 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 593 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 621 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: 593 Relay_Log_Space: 909 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: 2 1 row in set (0.00 sec) 在192.168.8.201创建用户: MariaDB [(none)]> grant replication slave,replication client on *.* to 'glq1'@'192.168.%.%' identified by '123123'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 245 | | mysql-bin.000002 | 28799 | | mysql-bin.000003 | 1069459 | | mysql-bin.000004 | 501 | +------------------+-----------+ 4 rows in set (0.00 sec) MariaDB [(none)]> change master to master_host='192.168.8.200',master_user='glq',master_password='123123',master_log_file='mysql-bin.000004',master_log_pos=500; Query OK, 0 rows affected (0.09 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.8.200 Master_User: glq Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 500 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 529 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: 500 Relay_Log_Space: 817 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: 1 1 row in set (0.00 sec) 测试: MariaDB [(none)]> create database mydata; //创建数据库; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mydata | | mysql | | performance_schema | | test | | testdb | +--------------------+ 6 rows in set (0.00 sec) MariaDB [(none)]> use mydata; Database changed MariaDB [mydata]> show tables; +------------------+ | Tables_in_mydata | +------------------+ | test | +------------------+ 1 row in set (0.00 sec) MariaDB [mydata]> desc test; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | CardID | varchar(20) | YES | | NULL | | +--------+---------------------+------+-----+---------+----------------+ 3 rows in set (0.07 sec) MariaDB [mydata]> insert into test(name,CardID)values('glq',1231223),('zyn',123123321); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 在200 server查看验证: MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mydata | | mysql | | performance_schema | | test | | testdb | +--------------------+ 6 rows in set (0.00 sec) MariaDB [(none)]> use mydata; Database changed MariaDB [mydata]> create table test(id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,name varchar(20) not null,CardID varchar(20)); Query OK, 0 rows affected (0.06 sec) MariaDB [mydata]> select * from test; +----+------+-----------+ | id | name | CardID | +----+------+-----------+ | 2 | glq | 1231223 | | 4 | zyn | 123123321 | +----+------+-----------+ 2 rows in set (0.00 sec) 至此主主复制配置完成