gitlab mysql 表_gitlab-mysql_高可用
### Mysql 高可用
Mysql 高可用采用雙主方案；
1. 兩個(gè) mysql節(jié)點(diǎn)，分別安裝在 192.168.1.247, 192.168.1.248；
2. 準(zhǔn)備\_Mysql01節(jié)點(diǎn) 修改配置文件，添加授權(quán)用戶(hù)
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 添加遠(yuǎn)程復(fù)制用戶(hù)
```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. 準(zhǔn)備\_Mysql02節(jié)點(diǎn) 修改配置文件，添加授權(quán)用戶(hù)
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 添加遠(yuǎn)程復(fù)制用戶(hù)
```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節(jié)點(diǎn)
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)
```
！！！請(qǐng)記住 “File” 和 “Position” 內(nèi)容稍后會(huì)用到！！！
4.2 鎖表
```bash
FLUSH TABLES WITH READ LOCK;
```
4.3 數(shù)據(jù)導(dǎo)出
```bash
root@ubuntu-server03:~# mysqldump -uroot -pxxx gitlabhq_production > gitlabhq_production.dump
```
4.4 解鎖
```bash
UNLOCK TABLES;
```
4.5 拷貝導(dǎo)出文件到Mysql02
```bash
scp gitlabhq_production.dump 192.168.1.248:/tmp/
```
5. 操作\_Mysql02節(jié)點(diǎn)
5.1 還原數(shù)據(jù)
```bash
mysql -uroot -p gitlabhq_production < /tmp/gitlabhq_production.dump
```
5.2 配置Mysql01 節(jié)點(diǎn)為主節(jié)點(diǎn)
```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 查看狀態(tài)
```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
這兩個(gè)參數(shù)都是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” 內(nèi)容！！！
6. 操作Mysql01節(jié)點(diǎn)
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 查看鏈接狀態(tài)
```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
這兩個(gè)參數(shù)都是Yes 代表成功！
7. 一個(gè)錯(cuò)誤
```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 ，然后鎖表，導(dǎo)出數(shù)據(jù)，將數(shù)據(jù)拷貝到slave并導(dǎo)入，修改slave參數(shù)，解鎖master 表，在次啟動(dòng)slave；
gitlab-mysql_高可用
標(biāo)簽：row???until???參數(shù)???res???添加???內(nèi)容???lock???配置連接???user
本條技術(shù)文章來(lái)源于互聯(lián)網(wǎng)，如果無(wú)意侵犯您的權(quán)益請(qǐng)點(diǎn)擊此處反饋版權(quán)投訴
本文系統(tǒng)來(lái)源：https://www.cnblogs.com/jin-yuana/p/9791896.html
總結(jié)
以上是生活随笔為你收集整理的gitlab mysql 表_gitlab-mysql_高可用的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： java邮箱设置密送_修改后可以发送附件
- 下一篇： java接口示例_【基础篇】java-接