Mysql主從配置

標籤：tool   error:   sts   condition   位置   測試   form   ase   iad   

1.Mysql主從配置環境資訊

Master：192.168.231.128

Slave：192.168.231.129

2.Matser配置

①編輯設定檔/etc/my.cnf

修改my.cnf，增加server-id=128和log_bin=test_01,隨後重新啟動mysql服務

[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!

②建立同步處理的使用者，並且將表暫時鎖定不允許再寫入

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)

③查看master的狀態資訊

此狀態顯示了master的bin_log檔案名稱以及當前所處的位置

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)

④將當前所有資料庫做備份

[[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: 無法訪問.tmp: 沒有那個檔案或目錄[[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配置

①編輯設定檔/etc/my.cnf添加server-id=129,隨後重新啟動mysqld服務

[[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: 無法訪問.tmp: 沒有那個檔案或目錄[[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!

②將Master上備份好的Database Backup檔案拷貝至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

③將Database Backup檔案恢複至對應的資料庫

[[email protected]_02 ~]# mysql -uroot [email protected]  wennan < /tmp/wennan.sql Warning: Using a password on the command line interface can be insecure.[[email protected]_02 ~]# mysql -uroot [email protected]  test < /tmp/test.sql Warning: Using a password on the command line interface can be insecure.

④登入資料庫，停止slave後修改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，查看slave狀態

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                    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)

⑥測試主從同步

在master建立表test1

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查看錶test1資訊，確認一致

mysql> select count(*) test1;+-------+| test1 |+-------+|     1 |+-------+1 row in set (0.00 sec)

Mysql主從配置