基于mysql8.0的雙主互備 · ubuntu系統上kubernetes集群實踐

# 基于Mysql8.0搭建mysql雙主互為主從 **據說mysql8.0比之前版本快兩倍，哈哈** * * * * * 環境：ubuntu18.04-server master1：192.168.11.241 master2：192.168.11.242 * * * * * ### 1.到官網下載對應的二進制軟件包，解壓縮到／usr/local/mysql ``` tar -xf mysql-8.0.12-linux-glibc2.12-x86_64.tar.xz mv mysql-8.0.12-linux-glibc2.12-x86_64 /usr/local/mysql ``` * * * * * ### 2.創建mysql 用戶，最好是不能登錄到，創建mysql組 groupadd mysql useradd -g mysql mysql ### 3.創建關于mysql的目錄： mkdir -pr /data/mysql cd /data/mysql mkdir {tmp,log,data,dumps,undo} ### 4. 給予相關目錄mysql的權限 chown mysql.mysql /usr/local/mysql -R chown mysql.mysql /data/mysql -R ### 5.導入環境變量 echo export PATH=$PATH:/usr/local/mysql/bin >> /etc/profile ### 6.一定要創建mysql的error.log ,mysql8.0啟動不會自動創建，而且沒有這個文件會報錯 touch /data/mysql/log/error.log ### 7.創建mysql的配置文件my.cnf 我們的my.cnf放在/usr/local/mysql/etc/my.cnf ``` Vim /etc/my.cnf [client] socket=/data/mysql/data/mysql.sock [mysqld] server-id=2 port = 3306 log-bin=/data/mysql/data/binlog basedir=/usr/local/mysql datadir=/data/mysql/data socket=/data/mysql/data/mysql.sock log-error=/data/mysql/log/mysqld.log pid-file=/data/mysql/data/mysqld.pid slow_query_log_file = /data/mysql/log/slow.log default_authentication_plugin=mysql_native_password ``` **這里也給出master2的my.cnf** ``` [client] socket=/data/mysql/data/mysql.sock [mysqld] server-id=3 port = 3306 log-bin=/data/mysql/data/binlog basedir=/usr/local/mysql datadir=/data/mysql/data socket=/data/mysql/data/mysql.sock log-error=/data/mysql/log/mysqld.log pid-file=/data/mysql/data/mysqld.pid slow_query_log_file = /data/mysql/log/slow.log default_authentication_plugin=mysql_native_password ``` * * * * * ### 8.初始化mysql，并且得到mysql的初始密碼在ubuntu系統中初始化時會提示找不到libao.so.1這個文件，我們可以通過如下操作來解決 apt-get update apt-get install libaio* 初始化操作 mysqld --initialize --basedir=/usr/local/mysql —datadir=/data/mysql/data 我們在日志中可以grep出我們的密碼 grep pass /data/mysql/log/mysqld.log 2018-09-03T05:42:56.253317Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Th<urDpY4tAl * * * * * ### 9.在配置好我們的my.cnf文件后，我們來啟動我們的mysql ``` mysqld_safe --defaults-file=/usr/local/mysql/etc/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data 2>&1 > /dev/null & root@test-db-11-242:/home/ubuntu# ss -tunlp |grep mysql tcp LISTEN 0 70 *:33060 *:* users:(("mysqld",pid=40113,fd=26)) tcp LISTEN 0 151 *:3306 *:* users:(("mysqld",pid=40113,fd=19)) 我們的mysql服務就啟動起來了 ``` * * * * * ### 10.我們為我們的mysql服務配置啟動項 cd /usr/local/mysql/support-files cp mysql.server /etc/init.d/ 下次啟動就可以通過/etc/init.d/mysql-servce restart 來啟動了 * * * * * ### 11.我們登錄到我們的mysql中，去為mysql主主復制來創建用戶并且授權 1.更改root的密碼 Use mysql; ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY ‘你的新密碼’; 2,創建用戶： create user 'user'@'%' identified by ‘密碼'; 3.授權： grant all on *.* to ‘user'@'%'; * * * * * ### 12.兩臺配置好了復制用戶并且授權以后，我們就來進行主從同步首先，把一臺當作主，一臺當作從現在主的上面執行： show master status/G 獲取二進制日志的偏移量，和 position ``` mysql> show master status; +---------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+------------------------------------------+ | binlog.000008 | 23486999 | | | 34fdc5ef-af3c-11e8-ba35-000c29dff316:1-5 | +---------------+----------+--------------+------------------+------------------------------------------+ ``` 在從上面執行： ``` master_host='172.16.63.129',master_user='slave',master_password='password',master_port=3306,MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=1068; ``` 注意對應的地方改為所查的值，在執行：start slave；然后在執行：show slave status; ``` mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.11.241 Master_User: tom Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000011 Read_Master_Log_Pos: 23484763 Relay_Log_File: test-db-11-242-relay-bin.000012 Relay_Log_Pos: 23482786 Relay_Master_Log_File: binlog.000011 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: 23484763 Relay_Log_Space: 23483167 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: 0 Master_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: 2 Master_UUID: 73b7dc2d-af36-11e8-bbf1-000c296da46c Master_Info_File: mysql.slave_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: 34fdc5ef-af3c-11e8-ba35-000c29dff316:1-5 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 1 row in set (0.00 sec) ``` 這就就算做好了主從復之，最后在把主的當成從的，從的當成主的，按上面的步奏操作，最后兩邊都可以看到兩個yes * * * * * ### 13.測試，往一臺上面寫數據，看另一臺能否同步。