MySQL master-slave replication configuration

Configure the primary database:my.cnf:server-id       = 1log-bin  Restart the database login and view the:[[email  protected] ~]# mysql -uroot -p199429mysql> show variables like  ' Log _bin ';   +---------------+-------+| variable_name | value |+---------------+----- --+| log_bin       | on    |+---------------+ -------+mysql> show variables like  ' server_id '; +---------------+-------+| variable _name | value |+---------------+-------+| server_id     | 1      |+---------------+-------+ Establish master-slave copy account:mysql> grant replication  slave on *.* to  ' rep ' @ ' 192.1.1.% '  identified by  ' 199429 ';mysql>  select user,host from mysql.user;+-----------+-----------+| user       |  host      |+-----------+-----------+| root       | 127.0.0.1 | |  bbs       | 192.1.1.% | |  keer      | 192.1.1.% | |  rep       | 192.1.1.% | |  wordpress | 192.1.1.% | |  root      | localhost |+-----------+-----------+ Implement lock table read-only:mysql> flush table with read lock; to the primary database query ok, 0 rows affected  (0.00 sec) mysql> show variables like   '%timeout% '; +----------------------------+----------+| variable_name               | value    |+------------------ ----------+----------+|&NBSP;CONNECT_TIMEOUT&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&Nbsp;  | 10       | |  delayed_insert_timeout     | 300      | |  innodb_lock_wait_timeout   | 50       | |  innodb_rollback_on_timeout | off      | |  interactive_timeout        | 28800    | #####| lock_wait_timeout          | 31536000  ||  net_read_timeout           | 30        | |  net_write_timeout          | 60        | |  slave_net_timeout          | 3600      | |  wait_timeout               | 28800     |#####+----------------------------+----------+10 rows in set  ( 0.00&NBSP;SEC) View Main Library Status: mysql> show master status;+-------------------------+----------+------- -------+------------------+| file                     | position | binlog_do_db | binlog_ ignore_db |+-------------------------+----------+--------------+------------------+|  mysql-server-bin.000001 |      962 |               |                   |+-------------------------+----------+--------------+-- ----------------+1&NBSP;ROW&NBSP;IN&NBsp;set  (0.00 sec) New window Backup export data: [[[email protected] ~]# mkdir -p /server/backup/  [[email protected] ~]# mysqldump -uroot -p199429 --events -a -b  |gzip >/server/backup/mysql_bak.$ (date +%f). sql.gz here to see if the state of the main library is changing:mysql> show  master status;+-------------------------+----------+--------------+------------------+| file                      | position | binlog_do_db | binlog_ignore_db |+-------------------------+- ---------+--------------+------------------+| mysql-server-bin.000001 |       962 |              |                   |+ -------------------------+----------+--------------+------------------+1 row in set  (0.00 sec) From database: Configuration file my.cnfserver-id       = 2  ### #保证唯一性重启从数据库登录从数据库: [[ Email protected]_02 backup]# mysql -uroot -p199429 -s /data/3306/mysql.sock  mysql> show variables like  ' log_bin '; +---------------+-------+| variable_ name | value |+---------------+-------+| log_bin        | off   |+---------------+-------+1 row in set  (0.00 sec) MySQL > show variables like  ' server_id '; +---------------+-------+| variable_name |  value |+---------------+-------+| server_id     | 3      |+---------------+-------+ recover mysqldump data [[EMAIL&NBSP;PROTECTED]_02&NBSP;BACKUP]#&NBSP;CD  /server/backup/[[email protected]_02 backup]# gzip -d mysql_bak.2017-03-23.sql.gz[[email protected]_02  Backup]# mysql -uroot -p ' 199429 '  -s /data/3306/mysql.sock <mysql_ bak.2017-03-23.sql  Login Database configuration replication parameters [[email protected]_02 backup]# mysql -uroot -p ' 199429 '  -s /data/3306/mysql.sockmysql> change master to    - > master_host= ' 192.1.1.11 ',    -> master_port=3306,     -> master_user= ' rep ',     -> master_password= ' 199429 ',     -> master_log_file= ' mysql-server-bin.000001 ',     -> master_log _pos=962; Fee Login Status Execution method: [[email protected]_02 backup]# mysql -uroot -p ' 199429 '  -S  /data/3306/mysql.sock<< eofchange master to    master_host= ' 192.1.1.11 ',     master_port=3306,    master_user= ' rep ',     master_password= ' 199429 ',     master_log_file= ' mysql-server-bin.000001 ',    master_log_pos=962; EOF actually modifies the Master.info file from the library [[Email protected]_02 backup]# cat /data/3306/data/master.info  18mysql-server-bin.000001962192.1.1.11rep1994293306600, ... Start master-slave replication:mysql> start slave;                                                  query ok, 0 rows affected  (0.00 sec) mysql> show slave status\g ;*************************** 1. row ***************************                slave_io_state:  waiting for master to send event                   Master_Host: 192.1.1.11                   Master_User:  rep                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: Mysql-server-bin.000001           Read_Master_Log_Pos: 962                Relay_Log_File: relay-bin.000002                 relay_log_pos: 260        relay_master_log_file:  Mysql-server-bin.000001              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: 962               Relay_Log_Space: 410               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: 11 row in set  (0.00 sec) test master-slave copy function (omitted) 

This article is from the "Trance" blog, make sure to keep this source http://bestlufeng.blog.51cto.com/11790256/1913415

MySQL master-slave replication configuration