gitlab mysql 表_gitlab-mysql_高可用
### Mysql 高可用
Mysql 高可用采用雙主方案；
1. 兩個 mysql節點，分別安裝在 192.168.1.247, 192.168.1.248；
2. 準備\_Mysql01節點 修改配置文件，添加授權用戶
2.1 修改mysql的配置文件，并重啟mysql
```bash
...
[mysqld]
...
#bind-address = 127.0.0.1
log-bin=mysql-bin
log-bin-index=mysql-bin.index
server-id = 1
...
```
2.2 添加遠程復制用戶
```mysql
mysql> grant replication slave on *.* to ‘slave‘@‘%‘ identified by ‘boxfish‘;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
```
3. 準備\_Mysql02節點 修改配置文件，添加授權用戶
3.1 修改mysql的配置文件，并重啟mysql
```bash
...
[mysqld]
...
#bind-address = 127.0.0.1
log-bin=mysql-bin
log-bin-index=mysql-bin.index
server-id = 2
...
```
3.2 添加遠程復制用戶
```mysql
mysql> grant replication slave on *.* to ‘slave‘@‘%‘ identified by ‘boxfish‘;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
```
4. 操作\_Mysql01節點
4.1 查看master status
```bash
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000013 | 1648933 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
```
！！！請記住 “File” 和 “Position” 內容稍后會用到！！！
4.2 鎖表
```bash
FLUSH TABLES WITH READ LOCK;
```
4.3 數據導出
```bash
root@ubuntu-server03:~# mysqldump -uroot -pxxx gitlabhq_production > gitlabhq_production.dump
```
4.4 解鎖
```bash
UNLOCK TABLES;
```
4.5 拷貝導出文件到Mysql02
```bash
scp gitlabhq_production.dump 192.168.1.248:/tmp/
```
5. 操作\_Mysql02節點
5.1 還原數據
```bash
mysql -uroot -p gitlabhq_production < /tmp/gitlabhq_production.dump
```
5.2 配置Mysql01 節點為主節點
```bash
mysql> change master to master_host=‘192.168.1.247’,
master_user=‘slave‘,
master_password=‘boxfish‘,
master_log_file=‘mysql-bin.000013‘,
master_log_pos=1648933;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql>start slave;
```
5.3 查看狀態
```bash
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.247
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000013
Read_Master_Log_Pos: 1866414
Relay_Log_File: mysql-relay-bin.000018
Relay_Log_Pos: 1866577
Relay_Master_Log_File: mysql-bin.000013
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: 1866414
Relay_Log_Space: 1866797
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: 0386913e-c1ce-11e6-82a0-3ca82a1de578
Master_Info_File: /var/lib/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: 0
1 row in set (0.00 sec)
mysql>
```
Slave\_IO\_Running: Yes
Slave\_SQL\_Running: Yes
這兩個參數都是Yes 代表成功！
5.4 查看自己的 master status !
```bash
mysql> show master status\g
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000010 | c | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
```
！！！記住“File”，“Position” 內容！！！
6. 操作Mysql01節點
6.1 配置連接到 mysql01 為 master
```bash
mysql> change master to master_host=‘192.168.1.248‘,
master_user=‘slave‘,
master_password=‘boxfish‘,
master_log_file=‘mysql-bin.000010‘,
master_log_pos=1958047;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
```
6.2 查看鏈接狀態
```bash
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.248
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 1958047
Relay_Log_File: mysql-relay-bin.000011
Relay_Log_Pos: 236
Relay_Master_Log_File: mysql-bin.000010
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: 1958047
Relay_Log_Space: 155297
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: 1
Master_UUID: 44b90af8-41c7-11e6-bfc9-5cb901fe49a4
Master_Info_File: /var/lib/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: 0
1 row in set (0.00 sec)
```
Slave\_IO\_Running: Yes
Slave\_SQL\_Running: Yes
這兩個參數都是Yes 代表成功！
7. 一個錯誤
```bash
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file‘
```
重新在主上面查看maste status ，然后鎖表，導出數據，將數據拷貝到slave并導入，修改slave參數，解鎖master 表，在次啟動slave；
gitlab-mysql_高可用
標簽：row???until???參數???res???添加???內容???lock???配置連接???user
本條技術文章來源于互聯網，如果無意侵犯您的權益請點擊此處反饋版權投訴
本文系統來源：https://www.cnblogs.com/jin-yuana/p/9791896.html
總結
以上是生活随笔為你收集整理的gitlab mysql 表_gitlab-mysql_高可用的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： java邮箱设置密送_修改后可以发送附件
- 下一篇： java接口示例_【基础篇】java-接