mysql主從安裝簡記

標籤：default   user   backup   uuid   cond   ted   log   tran   main   

安裝兩個 資料庫版本5.7.19 

10.168.21.180 gsm1 gsm1.localdomain
10.168.21.181 gsm2 gsm2.localdomain

安裝軟體
配置 /etc/my.cnf
建庫 mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql
啟動 mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &
更改密碼 set password = password(‘rootroot‘);
升級 mysql_upgrade -uroot -prootroot

主庫備份資料
mysqldump --single-transaction -uroot -prootroot -A --master-data=2 > alldb.sql
-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000002‘, MASTER_LOG_POS=550;

從庫恢複資料
scp alldb.sql gsm2:/root/backup
mysql -uroot -prootroot <alldb.sql

主庫授權：建立備份使用者
grant replication slave on *.* to ‘bak‘@‘10.168.%‘ identified by ‘rootroot‘;
flush privileges;

從庫配置複製起點並開始複製
CHANGE MASTER TO
MASTER_HOST=‘10.168.21.180‘,
MASTER_USER=‘bak‘,
MASTER_PASSWORD=‘rootroot‘,
MASTER_PORT=3306,
MASTER_LOG_FILE=‘mysql-bin.000002‘,
MASTER_LOG_POS=550;

start slave;

查看狀態

mysql> show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 10.168.21.180                  Master_User: bak                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000002          Read_Master_Log_Pos: 991               Relay_Log_File: gsm2-relay-bin.000002                Relay_Log_Pos: 761        Relay_Master_Log_File: mysql-bin.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: 991              Relay_Log_Space: 967              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: 33001                  Master_UUID: 8a0a909d-e469-11e7-95c0-005056b9c142             Master_Info_File: /data0/mysql/data/dbatest/master.info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates           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: 0         Replicate_Rewrite_DB:                  Channel_Name:            Master_TLS_Version: 1 row in set (0.00 sec)ERROR: No query specified

 

mysql主從安裝簡記