首页 / GIT / gitlab-mysql_高可用
gitlab-mysql_高可用
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了gitlab-mysql_高可用,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含8243字,纯文字阅读大概需要12分钟。
内容图文
![gitlab-mysql_高可用](/upload/InfoBanner/zyjiaocheng/501/0aefef61a3d143c3a933a268e98bab21.jpg)
### 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_高可用所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。