mysql innodb cluster (by quqi99)

浅浅的花香味﹌ 2022-08-31 07:18 149阅读 0赞

## 安装步骤 ##

在三个节点(juju deploy -n 3 ubuntu)上都运行：

apt install mysql-server mysql-router
    snap install mysql-shell
    sudo mysql -u root -p   #must use sudo
    update mysql.user set host = '%' where user = 'root';
    select host, user from mysql.user;
    CREATE USER 'rep1'@'%' IDENTIFIED BY 'password';
    GRANT ALL PRIVILEGES ON *.* TO 'rep1'@'%' WITH GRANT OPTION;
    FLUSH PRIVILEGES;
    # modify bind-address to 0.0.0.0 in all 3 nodes
    sudo sed -i 's/127.0.0.1/0.0.0.0/g' /etc/mysql/mysql.conf.d/mysqld.cnf
    sudo systemctl restart mysql

在其他两个节点上重复

# dba.configure_instance('rep1@juju-e57bf9-test-1:3306')
    # dba.configure_instance('rep1@juju-e57bf9-test-2:3306')
    root@juju-e57bf9-test-0:~# mysqlsh
    mysql-py> dba.configure_instance('rep1@juju-e57bf9-test-0:3306')
    Please provide the password for 'rep1@juju-e57bf9-test-0:3306': ********
    ...
    NOTE: Some configuration options need to be fixed:
    +----------------------------------------+---------------+----------------+--------------------------------------------------+
    | Variable                               | Current Value | Required Value | Note                                             |
    +----------------------------------------+---------------+----------------+--------------------------------------------------+
    | binlog_transaction_dependency_tracking | COMMIT_ORDER  | WRITESET       | Update the server variable                       |
    | enforce_gtid_consistency               | OFF           | ON             | Update read-only variable and restart the server |
    | gtid_mode                              | OFF           | ON             | Update read-only variable and restart the server |
    | server_id                              | 1             | <unique ID>    | Update read-only variable and restart the server |
    | slave_parallel_type                    | DATABASE      | LOGICAL_CLOCK  | Update the server variable                       |
    | slave_preserve_commit_order            | OFF           | ON             | Update the server variable                       |
    +----------------------------------------+---------------+----------------+--------------------------------------------------+
    Do you want to perform the required configuration changes? [y/n]: y
    Do you want to restart the instance after configuring it? [y/n]: y
    Configuring instance...
    The instance 'juju-e57bf9-test-0:3306' was configured to be used in an InnoDB cluster.
    Restarting MySQL...
    NOTE: MySQL server at juju-e57bf9-test-0:3306 was restarted.

只在ubuntu/0上创建cluster.

mysql-py> shell.connect('rep1@juju-e57bf9-test-0:3306')
    # This sets the group_replication_group_name system variable
    mysql-py []> cluster = dba.create_cluster('jujuCluster')
    mysql-py []> cluster.add_instance('rep1@juju-e57bf9-test-1:3306')
    mysql-py []> cluster.add_instance('rep1@juju-e57bf9-test-2:3306')
    mysql-py []> cluster.status()
    mysql-py []> cluster.describe();
    mysql-py []> cluster.rescan();
    mysql-py []> cluster.check_instance_state('rep1@juju-e57bf9-test-0:3306');
    mysql-py []> \sql
    mysql-sql []> SELECT member_host, member_port, member_state, member_role FROM performance_schema.replication_group_members;
    +--------------------+-------------+--------------+-------------+
    | member_host        | member_port | member_state | member_role |
    +--------------------+-------------+--------------+-------------+
    | juju-e57bf9-test-1 |        3306 | ONLINE       | SECONDARY   |
    | juju-e57bf9-test-2 |        3306 | ONLINE       | SECONDARY   |
    | juju-e57bf9-test-0 |        3306 | ONLINE       | PRIMARY     |
    +--------------------+-------------+--------------+-------------+
    
    # Persist cluster configuration
    dba.configure_local_instance('rep1@juju-e57bf9-test-0:3306');
    dba.configure_local_instance('rep1@juju-e57bf9-test-1:3306');
    dba.configure_local_instance('rep1@juju-e57bf9-test-2:3306');
    
    # On non-leader nodes. eg:
    mysqlsh rep1@juju-e57bf9-test-1:33060 -ppassword
    #shell.connect('rep1@juju-e57bf9-test-1:3306')
    cluster = dba.get_cluster('jujuCluster');
    
    # Use mysql router
    mysqlrouter --bootstrap rep1@juju-e57bf9-test-0:3306 --directory myrouter
    myrouter/start.sh
    mysqlsh rep1@juju-e57bf9-test-0:6446
    mysqlsh rep1@juju-e57bf9-test-0:6447  #read-only

连接cluster：

juju ssh ubuntu/0 -- mysqlsh rep1@juju-e57bf9-test-1:33060 -ppassword --cluster
    juju ssh ubuntu/1 -- mysqlsh rep1@juju-e57bf9-test-1:33060 -ppassword --cluster
    juju ssh ubuntu/2 -- mysqlsh rep1@juju-e57bf9-test-1:33060 -ppassword --cluster

## 问题 ##

操作之前记得备份．

juju run-action --wait mysql-innodb-cluster/1 mysqldump  #/var/backups/mysql
    mysqldump --all-databases --single-transaction --quick --lock-tables=false > full-backup-$(date +%F).sql -u clusteruser -pM6BsjpJVzYsy74L5gxqBHnr5V9mmwJNWdCbYBdN
    juju ssh mysql-innodb-cluster/1 sudo du -ah /var/backups/mysql/ /var/lib/mysql | tee -a mysql-innodb-cluster-1-backups.txt
    SHOW VARIABLES LIKE 'gtid_executed';

一般平时关一个节点，那样cluster不会脑裂, 一般下面也能恢复．

# https://lefred.be/content/mysql-innodb-cluster-how-to-manage-a-split-brain-situation/
    cluster = dba.get_cluster('jujuCluster');
    cluster.force_quorum_using_partition_of('rep1@juju-e57bf9-test-0:3306')
    cluster.rejoin_instance('rep1@juju-e57bf9-test-0:3306')

发生outage了，三个节点同时关了，可能起来就一个cluster脑裂成3个了．这时一般通过reboot\_cluster\_from\_complete\_outage也能解决．

mysql-py []> cluster = dba.reboot_cluster_from_complete_outage();

但我三分列成3个cluster的情况下又遇到两个问题：

*  之前的leader可能变成现在的non-leader，它再replication时容易状态变成error
 *  根据网上一篇错误文章删了重要的表mysql\_innodb\_cluster\_metadata，它也变成error  
    通过下列命令看三个节点各自为政都是offline容易通过reboot\_cluster\_from\_complete\_outage恢复，但涉及到复制导致error状态了就很难了．是不是要删除了数据目录重建呢？

\sql
    SHOW STATUS LIKE 'group_replication_primary_member';
    SELECT * FROM performance_schema.replication_group_members;

其他可能遇得到的命令：

\sql
    SET SQL_LOG_BIN = 0;
    stop group_replication;
    set global super_read_only=0;
    # after drop database, Access denied for user 'mysql_innodb_cluster'
    # drop database mysql_innodb_cluster_metadata;
    \use mysql_innodb_cluster_metadata
    DELETE FROM instances WHERE address = ‘juju-e57bf9-test-1:3306’;
    DELETE FROM v2_instances WHERE address = ‘juju-e57bf9-test-1:3306’;
    \use mysql
    DELETE FROM user WHERE Host = 'juju-e57bf9-test-1';
    DELETE FROM user WHERE User = 'mysql_innodb_cluster_4097976713';
    RESET MASTER;
    RESET REPLICA ALL;
    select * from performance_schema.replication_group_members;
    SELECT @@group_replication_local_address;
    #Be careful that the best practice is to shutdown the other nodes
    #https://lefred.be/content/mysql-innodb-cluster-how-to-manage-a-split-brain-situation/
    set global group_replication_force_members=@@group_replication_local_address;
    set global group_replication_force_members=''
    start group_replication;
    
    \py
    cluster = dba.get_cluster('jujuCluster');
    cluster = dba.get_cluster();
    # since we drop mysql_innodb_cluster_metadata, so we need to first remove_instance
    # then add_instance to rebuild mysql_innodb_cluster_metadata
    cluster.remove_instance('rep1@juju-e57bf9-test-1:3306', {'force': 'true'});
    # then remember to select 'Clone'
    cluster.add_instance('rep1@juju-e57bf9-test-1:3306');

注：对于已经脑裂的节点在remove再add之前可能还需要stop replication. (尚未测试）

## 其他 ##

一些重要的数据结构．

mysql-sql [mysql_innodb_cluster_metadata]> show tables;
    +-----------------------------------------+
    | Tables_in_mysql_innodb_cluster_metadata |
    +-----------------------------------------+
    | async_cluster_members                   |
    | async_cluster_views                     |
    | clusters                                |
    | instances                               |
    | router_rest_accounts                    |
    | routers                                 |
    | schema_version                          |
    | v2_ar_clusters                          |
    | v2_ar_members                           |
    | v2_clusters                             |
    | v2_gr_clusters                          |
    | v2_instances                            |
    | v2_router_rest_accounts                 |
    | v2_routers                              |
    | v2_this_instance                        |
    +-----------------------------------------+
    
    mysql-sql [mysql_innodb_cluster_metadata]> select attributes from v2_instances \G
    *************************** 1. row ***************************
    attributes: {"server_id": 4097976713, "recoveryAccountHost": "%", "recoveryAccountUser": "mysql_innodb_cluster_4097976713"}
    1 row in set (0.0010 sec)