MySQL Master-slave configuration

1.Mysql Master-Slave configuration environment Information

master:192.168.231.128

slave:192.168.231.129

2.Matser Configuration

① Editing a configuration file/etc/my.cnf

Modify MY.CNF, increase add server-id=128 and log_bin=test_01, and then restart the MySQL service

[mysqld]datadir=/data/mysqlsocket=/tmp/mysql.sockserver-id=128log_bin=test_01# disabling  symbolic-links is recommended to prevent assorted security  riskssymbolic-links=0# settings user and group are ignored when  systemd is used.# if you need to run mysqld under a  Different user or group,# customize your systemd unit file for  mariadb according to the# instructions in http://fedoraproject.org/wiki/ Systemd[mysqld_safe] #log-error=/var/log/mariadb/mariadb.log#pid-file=/var/run/mariadb/mariadb.pid##  include all files from the config directory##!includedir /etc/my.cnf.d[[ email protected]_01 ~]# /etc/init.d/mysqld restartshutting down mysql...  success! starting mysql. success!

② Create a synchronization user, and temporarily lock the table is not allowed to write again

mysql> grant replication Slave on * * to ' replicate ' @ ' 192.168.231.129 ' identified by ' [email protected] '; Query OK, 0 rows Affected (0.00 sec) mysql> flush tables with read lock; Query OK, 0 rows affected (0.01 sec)

③ viewing status information for master

This status shows the Bin_log file name of master and the location where it is currently located

mysql> show master status    ->  ;+----------------+----------+--------------+------------------+-------------------+| file            | position | binlog_do_db |  binlog_ignore_db | executed_gtid_set |+----------------+----------+--------------+------- -----------+-------------------+| test_01.000002 |   653120 |               |                   |                    |+----------------+----------+-------- ------+------------------+-------------------+1 row in set  (0.00 sec) 

④ all current databases to be backed up

[[email protected]_01 mysql]# lsauto.cnf  ib_logfile0  mysql                test             test_01.000002  test_01.index   wennanibdata1   ib_logfile1  performance_schema  test_01.000001   test_01.err     test_01.pid[[email protected]_01 mysql]# ls . tmpls:  cannot access .tmp:  does not have that file or directory [[email protected]_01 mysql]# ls /tmpmysql.sock   mysql.sql  systemd-private-e3f448a092a245e78ad7f85feac7cd9a-vmtoolsd.service-hisgs1[[email  protected]_01 mysql]# mysqldump -uroot [email protected] test >  /tmp/test.sqlWarning: Using a password on the command line  Interface can be insecure. [[Email protected]_01 mysql]# mysqldump -uroot [email protected] wennan  > /tmp/wennan.sqlwarning: using a password on the command line  interface can be insecure. [[email protected]_01 mysql]# ls /tmpmysql.sock  mysql.sql   systemd-private-e3f448a092a245e78ad7f85feac7cd9a-vmtoolsd.service-hisgs1  test.sql   Wennan.sql

3.Slave Configuration

① Edit the configuration file/etc/my.cnf add server-id=129, and then restart the MYSQLD service

[[email protected]_01 mysql]# lsauto.cnf  ib_logfile0  mysql                test             test_01.000002  test_01.index   wennanibdata1   ib_logfile1  performance_schema  test_01.000001   test_01.err     test_01.pid[[email protected]_01 mysql]# ls . tmpls:  cannot access .tmp:  does not have that file or directory [[email protected]_01 mysql]# ls /tmpmysql.sock   mysql.sql  systemd-private-e3f448a092a245e78ad7f85feac7cd9a-vmtoolsd.service-hisgs1[[email  protected]_01 mysql]# mysqldump -uroot [email protected] test >  /tmp/test.sqlWarning: Using a password on the command line  Interface can be insecure. [[Email protected]_01 mysql]# mysqldump -uroot [email protected] wennan  > /tmp/wennan.sqlwarning: using a password on the command line  interface can be insecure. [[email protected]_01 mysql]# ls /tmpmysql.sock  mysql.sql   systemd-private-e3f448a092a245e78ad7f85feac7cd9a-vmtoolsd.service-hisgs1  test.sql   wennan.sql[[email protected]_02 ~]# /etc/init.d/mysqld restartshutting down  Mysql..  success! starting mysql. success!

② Copy the backed up database backup files from master to slave

[[Email protected]_02 ~]# scp 192.168.231.128:/tmp/*.sql /tmpthe authenticity  of host  ' 192.168.231.128  (192.168.231.128) '  can ' t be established. ecdsa key fingerprint is b2:0d:32:2b:8f:40:2e:c2:2d:ab:6d:f0:2c:6b:2d:9a. are you sure you want to continue connecting  (yes/no)?  yPlease  type  ' yes '  or  ' no ': yeswarning: permanently added  ' 192.168.231.128 '   (ECDSA)  to the list of known hosts. [email protected] ' s password:                                                                                              100%  637KB 636.7KB/s    00:00    test.sql                                                                                                100% 1258     1.2KB/s    00:00    wennan.sql

③ Restoring a database backup file to a corresponding database

[Email protected]_02 ~]# mysql-uroot [email protected] Wennan </tmp/wennan.sql warning:using a password on the COM Mand line interface can is insecure.  [Email protected]_02 ~]# mysql-uroot [email protected] Test </tmp/test.sql warning:using a password on the command Line interface can insecure.

④ login database, modify slave parameters after stopping slave

Mysql>stop Slave;mysql>change Master to master_host= ' 192.168.231.128 ', master_user= ' replicate ', master_ password= ' [email protected] ', master_log_file= ' test_01.000002 ', master_log_pos=653120,

⑤ slave again to view the slave status

mysql> start slave;mysql>  show slave stauts\gerror 1064  (42000) : you have an error in your sql syntax; check the  Manual that corresponds to your mysql server version for the  right syntax to use near  ' stauts '  at line 1mysql>   show slave status\g*************************** 1. row ***************************                slave_io_state:  Waiting for master to send event                   Master_Host: 192.168.231.128                   master_ User: replicate                  master_port: 3306                 connect_retry:  60              master_log_file:  test_01.000002          Read_Master_Log_Pos:  653120               relay_log_file : test_02-relay-bin.000002                 relay_log_pos: 281        relay_master_log_file :  test_01.000002             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: 653120               relay_log_space: 456               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: 128                   Master_UUID: 40078eac-e0ed-11e7-92e6-000c296b046f              master_info_file: /data/mysql/ Master.info    &nbsP;               sql_delay: 0           SQL_Remaining_Delay: NULL       Slave_SQL_Running_State: Slave has read all relay log;  waiting for the slave i/o thread to update it            Master_Retry_Count: 86400                   Master_Bind:        Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:                 MASTER_SSL_CRL:             Master_SSL_Crlpath:             retrieved_gtid_set:              Executed_Gtid_Set:                  Auto_Position: 01 row in set  (0.00 SEC)

⑥ Test Master-Slave synchronization

CREATE TABLE Test1 in master

Mysql> CREATE TABLE test1 (' id ' int (4), ' name ' char (40)); Query OK, 0 rows affected (0.39 sec) mysql> Select COUNT (*) test1;+-------+|     Test1 |+-------+| 1 |+-------+1 row in Set (0.00 sec)

Slave viewing table test1 information, confirming consistency

Mysql> Select COUNT (*) test1;+-------+|     Test1 |+-------+| 1 |+-------+1 row in Set (0.00 sec)

MySQL Master-slave configuration