MySQL's master-slave replication

MySQL's master-slave replication

Node One

Modify configuration file Settings unique ID open binary log

[[email protected] ~]# VIM/ETC/MY.CNF Add the following [mysqld] log-bin=master_bin open binary log server_id=1 to the primary node a unique ID number Innodb_file_per_table=on InnoDB open Independent table space Skip_name_resolve=on turn on bypass hostname reverse Solution

[[Email protected] ~]# service mariadb start[[email protected] ~]# mysqlmariadb [(None)]> show global variables like '%   log% ';    Check if binary log log_bin is turned on mariadb [(none)]> show global variables like '%server% '; See if the di number is 1MariaDB [(none)]> show master logs; View the location of the primary node binary log, starting from the node where the last log from the primary node is copied mariadb [(none)]> Grant replication Slave,replication Client on * * to ' copy ' @ '     192.168.%.% ' identified by ' passwd '; Create and authorize a remote copy account copy password for passwdmariadb [(none)]> flush privileges; Refresh User Permissions

Node two

[[email protected] ~]# vim /etc/my.cnf    relay_log=relay_log  Open Trunk Log     relay-log-index=relay-log.index     server_id=2       also need to set a unique ID number     innodb_file_per_table=on     skip_name_resolve=on[[email protected] ~]# service mariadb start[[email  protected] ~]# mysqlmariadb [(none)]> show global variables like  '% log% ';    see if the trunk log relay_log is open mariadb [(none)]> show global variables  like  '%server% ';     see if the ID number is 2 for the primary node is 192.168.1.107, the remote copy account is copy, the password is passwd, The copy binary log starts at 245 mariadb [(none)]> change master to master_host= ' in the starting position of 000003 192.168.1.107 ', master_user= ' copy ', master_password= ' passwd ', master_log_file= ' master_bin.000003 ', master_log_pos= 245; mariadb [(None)]> start slave;   Boot from node replication thread mariadb [(none)]> show slave status\g;************************  1. row ***************************                Slave_IO_State: Waiting for master to send  event                   Master_Host: 192.168.1.107                   Master_User: copy                   Master_Port: 3306                 Connect_Retry: 60               master_log_file: master_ bin.000003          read_master_log_pos: 491                Relay_Log_File: relay_log.000003                 Relay_Log_Pos: 776         Relay_Master_Log_File: master_bin.000003              Slave_IO_Running: Yes   These two items must be yes             Slave_SQL_Running: Yes   These two items must be 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: 491               Relay_Log_Space: 1064               Until_Condition: None                Until_Log_File:                &Nbsp; 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: 11 row in set  (0.00 SEC)

Attention

If Slave_IO_Running the solution is not yes

Example: ERROR 1201 (HY000)

MariaDB [(none)]> slave stop;  Stop MARIADB from Node [(none)]> reset slave; Reset Slave node

Find a problem where the settings are being re-authorized from the node

MariaDB [(none)]> change master to master_host= ' 192.168.1.107 ', master_user= ' copy ', master_password= ' passwd ', Master_log_file= ' master_bin.000003 ', master_log_pos=245;  MariaDB [(None)]> start slave;  Start from Node mariadb [(None)]> show Slave status\g; View status

Note that the write operation must not be done from the node

Verify

Master Node

MariaDB [(None)]> CREATE database msdb; MariaDB [msdb]> CREATE TABLE xx (id int (4) NOT NULL Auto_increment,name varchar (+) not null,primary key (ID)) engine=in Nodb Charset=utf8; MariaDB [msdb]> insert INTO XX (id,name) VALUES (1, ' King ');

From the node

mariadb [(None)]> show databases;+--------------------+| database            |+--------------------+| information_schema | |  msdb               | |  mysql              | |  performance_schema | |  test               |+-------- ------------+mariadb [(None)]> use msdb; mariadb [msdb]> show tables;+----------------+| tables_in_msdb |+----------------+ | xx             |+----------------+ mariadb [msdb]> select * from xx;+----+------+| id | name |+-- --+------+|  1 | king |+----+------+

This article is from the "Wind" blog, please be sure to keep this source http://xsllqs.blog.51cto.com/2308669/1827609

MySQL's master-slave replication