masayuki14
commented
6 years ago vagrant ubuntu で試す。Vagrantfile に vim, tmux などの provision をshell でやる。
Closed masayuki14 closed 6 years ago
masayuki14
commented
6 years ago vagrant ubuntu で試す。Vagrantfile に vim, tmux などの provision をshell でやる。
masayuki14
commented
6 years ago 各モジュールのダウンロードもshellでプロビジョニングする。
masayuki14
commented
6 years ago community-server を DLする https://dev.mysql.com/downloads/mysql/
$ cat /etc/lsb-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=16.04
DISTRIB_CODENAME=xenial
DISTRIB_DESCRIPTION="Ubuntu 16.04.3 LTS"Ubuntu Linux 16.04 (x86, 64-bit), DEB Bundle でいいかしら。 https://dev.mysql.com/downloads/file/?id=474544 https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-server_5.7.21-1ubuntu16.04_amd64.deb-bundle.tar
masayuki14
commented
6 years ago ちがう。 Linux - Generic から選ぶべき
masayuki14
commented
6 years ago ダウンロード、展開、パス通すとコマまで Vagrantfile に書いた。
masayuki14
commented
6 years ago さて、準備はととのった。
masayuki14
commented
6 years ago MySQL Shell でサンドボックスのインスタンをたてる。 working dir はどこだ？
masayuki14
commented
6 years ago sudo -i mysqlsh を試す。
mysql-js> dba.deploySandboxInstance(3310)
A new MySQL sandbox instance will be created on this host in
/root/mysql-sandboxes/3310
Warning: Sandbox instances are only suitable for deploying and
running on your local machine for testing purposes and are not
accessible from external networks.
Please enter a MySQL root password for the new instance:
Deploying new MySQL instance...
Dba.deploySandboxInstance: ERROR: Error creating sandbox: Could not verify mysqld executable: Unable to parse version output '' from mysqld executable '/usr/local/mysql/bin/mysqld'. Error executing '/usr/local/mysql/bin/mysqld --version': /usr/local/mysql/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
. (RuntimeError)
mysql-js>エラーでうまくいかない。
masayuki14
commented
6 years ago 3310 ポートでサンドボックスのMySQLサーバーを立てようとするがうまくいかない。 Why??
masayuki14
commented
6 years ago libaio.so が無いからインストールが必要
$ sudo apt-get install libaio1Vagrantfie に追記しておく
masayuki14
commented
6 years ago すると成功する。
mysql-js> dba.deploySandboxInstance(3310)
A new MySQL sandbox instance will be created on this host in
/root/mysql-sandboxes/3310
Warning: Sandbox instances are only suitable for deploying and
running on your local machine for testing purposes and are not
accessible from external networks.
Please enter a MySQL root password for the new instance:
Deploying new MySQL instance...
Instance localhost:3310 successfully deployed and started.
Use shell.connect('root@localhost:3310'); to connect to the instance.root になってsandboxが出来ているか確認する。
# sudo su -
# pwd
/root
# ls
mysql-sandboxes/
# cd mysql-sandboxes/
# ls
3310/続けて 3320 3330 のSandboxをつくる。
masayuki14
commented
6 years ago mysql-js> dba.deploySandboxInstance(3320)
mysql-js> dba.deploySandboxInstance(3330)# pwd
/root/mysql-sandboxes
# ls
3310/ 3320/ 3330/3つのSandboxへぷろんプロとをつないでおく
$ mysql -u root -p -h 127.0.0.1 -P3310 --prompt="mysql-\p>"
$ mysql -u root -p -h 127.0.0.1 -P3320 --prompt="mysql-\p>"
$ mysql -u root -p -h 127.0.0.1 -P3330 --prompt="mysql-\p>"・各インスタンスに グループ・レプリケーションを設定 ・シングル・プライマリーモードで構成 され、プライマリーインスタンスの ポートは3310になる
masayuki14
commented
6 years ago 3310 に接続してグループレプリケーションのクラスタを作る。 デフォルトのままだと 3310 をマスタにしたシングルマスターモードになる。
mysql-js> shell.connect('root@localhost:3310') [0/539]
Please provide the password for 'root@localhost:3310':
Creating a Session to 'root@localhost:3310'
Your MySQL connection id is 10
No default schema selected; type \use <schema> to set one.
mysql-js>
mysql-js> cluster
ReferenceError: cluster is not defined
mysql-js> cluster = dba.createCluster('mycluster')
A new InnoDB cluster will be created on instance 'root@localhost:3310'.
Creating InnoDB cluster 'mycluster' on 'root@localhost:3310'...
Adding Seed Instance...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.
<Cluster:mycluster>設定すると、グループレプリケーション関連のパラメータが設定されるので、show variables で見ると分かる。
mysql-3310> show variables like '%group%';
+----------------------------------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------------------------+--------------------------------------+
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| group_concat_max_len | 1024 |
| group_replication_allow_local_disjoint_gtids_join | OFF |
| group_replication_allow_local_lower_version_join | OFF |
| group_replication_auto_increment_increment | 7 |
| group_replication_bootstrap_group | OFF |
続けて3320 3330 をクラスタに追加する。
mysql-3320> show variables like '%group%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| group_concat_max_len | 1024 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| slave_checkpoint_group | 512 |
+-----------------------------------------+-------+
6 rows in set (0.00 sec)
mysql-3330> show variables like '%group%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| group_concat_max_len | 1024 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| slave_checkpoint_group | 512 |
+-----------------------------------------+-------+
6 rows in set (0.00 sec)追加済のサーバーを指定しても大丈夫。
mysql-js> cluster.addInstance('root@localhost:3310')
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
Please provide the password for 'root@localhost:3310':
Adding instance to the cluster ...
Cluster.addInstance: The instance 'localhost:3310' is already part of this InnoDB cluster (RuntimeError)status() describe() で情報がみれる。
mysql-js> cluster.describe()
{
"clusterName": "mycluster",
"defaultReplicaSet": {
"instances": [
{
"host": "localhost:3310",
"label": "localhost:3310",
"role": "HA"
}
],
"name": "default"
}
}
mysql-js> cluster.status()
{
"clusterName": "mycluster",
"defaultReplicaSet": {
"name": "default",
"primary": "localhost:3310",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"localhost:3310": {
"address": "localhost:3310",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
}
}ちゃんと追加する。
mysql-js> cluster.addInstance('root@localhost:3320')
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
Please provide the password for 'root@localhost:3320':
Adding instance to the cluster ...
The instance 'root@localhost:3320' was successfully added to the cluster.
mysql-js> cluster.addInstance('root@localhost:3330')
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
Please provide the password for 'root@localhost:3330':
Adding instance to the cluster ...
The instance 'root@localhost:3330' was successfully added to the cluster.
mysql-js> cluster.describe()
{
"clusterName": "mycluster",
"defaultReplicaSet": {
"instances": [
{
"host": "localhost:3310",
"label": "localhost:3310",
"role": "HA"
},
{
"host": "localhost:3320",
"label": "localhost:3320",
"role": "HA"
},
{
"host": "localhost:3330",
"label": "localhost:3330",
"role": "HA"
}
],
"name": "default"
}
}
mysql-js> cluster.status()
{
"clusterName": "mycluster",
"defaultReplicaSet": {
"name": "default",
"primary": "localhost:3310",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"localhost:3310": {
"address": "localhost:3310",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"localhost:3320": {
"address": "localhost:3320",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"localhost:3330": {
"address": "localhost:3330",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
}
}この時点で show variables like '%group%' を実行するとすべてのサーバーでグループレプリケーションのパラメータが設定されていることが確認できる。
masayuki14
commented
6 years ago この時点でグループレプリケーションは設定完了しているのかな？
masayuki14
commented
6 years ago 3つとも同じ5つのDatabase
mysql-3330> show databases;
+-------------------------------+
| Database |
+-------------------------------+
| information_schema |
| mysql |
| mysql_innodb_cluster_metadata |
| performance_schema |
| sys |
+-------------------------------+
5 rows in set (0.01 sec)mysql-3310> create database mycluster;
Query OK, 1 row affected (0.00 sec)
mysql-3310>
mysql-3310> show databases;
+-------------------------------+
| Database |
+-------------------------------+
| information_schema |
| mycluster |
| mysql |
| mysql_innodb_cluster_metadata |
| performance_schema |
| sys |
+-------------------------------+
6 rows in set (0.00 sec)マスターになっている 3310 で mycluster Database をつくると 3320 3330 にも反映されていることが分かる。
mysql-3320> show databases;
+-------------------------------+
| Database |
+-------------------------------+
| information_schema |
| mycluster |
| mysql |
| mysql_innodb_cluster_metadata |
| performance_schema |
| sys |
+-------------------------------+
6 rows in set (0.00 sec)
mysql-3330> show databases;
+-------------------------------+
| Database |
+-------------------------------+
| information_schema |
| mycluster |
| mysql |
| mysql_innodb_cluster_metadata |
| performance_schema |
| sys |
+-------------------------------+
6 rows in set (0.00 sec)
mysql-3310> use mycluster;
Database changed
mysql-3310> create table `mytable` (
-> `id` int auto_increment,
-> `name` varchar(64),
-> primary key (`id`)
-> )
-> ;
Query OK, 0 rows affected (0.02 sec)mysql-3320> show tables;
+---------------------+
| Tables_in_mycluster |
+---------------------+
| mytable |
+---------------------+
1 row in set (0.00 sec)
mysql-3320> show create table mytable \G
*************************** 1. row ***************************
Table: mytable
Create Table: CREATE TABLE `mytable` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)テーブルもできてる。
3302 でデータを登録する。
mysql-3320> insert into mytable (`name`) values ('morisaki');
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement3320 はReadOnlyなので INSERT できない。
mysql-3310> insert into mytable (`name`) values ('morisaki');
Query OK, 1 row affected (0.05 sec)mysql-3320> select * From mytable;
+----+----------+
| id | name |
+----+----------+
| 1 | morisaki |
+----+----------+
1 row in set (0.00 sec)MySQL Routerの設定、MySQL Routerの起動
- 作業ディレクトリ(mysqlユーザーが書き込み可能なディレクトリ)に移動し、 MySQL Routerの設定を行い、その後起動する
- mysqlrouter --bootstrap 実行時はMySQLのrootユーザーのパスワードを入力 (3310ポートで稼働しているMySQLインスタンスのrootユーザー)
cd /work
mysqlrouter --bootstrap localhost:3310 --user=mysql --derectory myrouter
myrouter/start.sh
masayuki14
commented
6 years ago masayuki14
commented
6 years ago セットアップは root じゃなくてもいいのかな。
masayuki14
commented
6 years ago とりあえず ubuntu ユーザーで work/ 作ってそこでやる。
masayuki14
commented
6 years ago まずヘルプ見る
$ mysqlrouter --helpけっこうな量がある。
masayuki14
commented
6 years ago Examples:
Bootstrap for use with InnoDB cluster into system-wide installation
sudo mysqlrouter --bootstrap root@clusterinstance01 --user=mysqlrouter
Start router
sudo mysqlrouter --user=mysqlrouter&
Bootstrap for use with InnoDb cluster in a self-contained directory
mysqlrouter --bootstrap root@clusterinstance01 -d myrouter
Start router
myrouter/start.sh抜粋
-B <server_url>, --bootstrap <server_url>
Bootstrap and configure Router for operation with a MySQL InnoDB
cluster.
-d <directory>, --directory <directory>
Creates a self-contained directory for a new instance of the
Router. (bootstrap)
-u <username>, --user <username>
Run the mysqlrouter as the user having the name user_name.ユーザーはやはり root か。
（サンプルコード # cd /work が#から始まってるため）
masayuki14
commented
6 years ago $ mysqlrouter --bootstrap localhost:3310 --user=mysql --directory myrouter
Error: One can only use the -u/--user switch if running as rootやはりrootでやれって言われる。
masayuki14
commented
6 years ago $ sudo -i fish
# pwd
/root
# mkdir work
# cd work# mysqlrouter --bootstrap localhost:3310 --user=mysql --directory myrouter
Please enter MySQL password for root:
Bootstrapping MySQL Router instance at /root/work/myrouter...
MySQL Router has now been configured for the InnoDB cluster 'mycluster'.
The following connection information can be used to connect to the cluster.
Classic MySQL protocol connections to cluster 'mycluster':
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447
X protocol connections to cluster 'mycluster':
- Read/Write Connections: localhost:64460
- Read/Only Connections: localhost:64470
Error: Could not access the config file as user 'mysql' after the bootstrap in the directory myrouter : Operation not permittedmysqlのrootパスワード要求される。
masayuki14
commented
6 years ago mysql ユーザーが書き込み可能なディレクトリにしないといけない。
masayuki14
commented
6 years ago # chown root.mysql .
# chmod 775 .
# mysqlrouter --bootstrap localhost:3310 --user=mysql --directory myrouter
Please enter MySQL password for root:
Bootstrapping MySQL Router instance at /root/work/myrouter...
Error: It appears that a router instance named '' has been previously configured in this host. If that instance no longer exists, use the --force option to overwrite it./root/work を /work に移動する。 /root 直下だとみえない。
# mv work/ /
# cd work
# mysqlrouter --bootstrap localhost:3310 --user=mysql --directory myrouter
Please enter MySQL password for root:
Bootstrapping MySQL Router instance at /root/work/myrouter...
Error: It appears that a router instance named '' has been previously configured in this host. If that instance no longer exists, use the --force option to overwrite it.--name で名前を指定する。
# mysqlrouter --bootstrap localhost:3310 --user=mysql --directory myrouter --name myrouter
Please enter MySQL password for root:
Bootstrapping MySQL Router instance at /work/myrouter...
Error: It appears that a router instance named 'myrouter' has been previously configured in this host. If that instance no longer exists, use the --force option to overwrite it.同じエラーだなー。
masayuki14
commented
6 years ago # mysqlrouter --bootstrap localhost:3310 --user=mysql
Please enter MySQL password for root:
Bootstrapping system MySQL Router instance...
MySQL Router has now been configured for the InnoDB cluster 'mycluster'.
The following connection information can be used to connect to the cluster.
Classic MySQL protocol connections to cluster 'mycluster':
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447
X protocol connections to cluster 'mycluster':
- Read/Write Connections: localhost:64460
- Read/Only Connections: localhost:64470出来たっぽいけど、どこにできたんずらー。
masayuki14
commented
6 years ago # mysqlrouter --bootstrap localhost:3310 --user=mysql --directory myrouter --force
Please enter MySQL password for root:
Reconfiguring MySQL Router instance at /work/myrouter...
MySQL Router has now been configured for the InnoDB cluster 'mycluster'.
The following connection information can be used to connect to the cluster.
Classic MySQL protocol connections to cluster 'mycluster':
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447
X protocol connections to cluster 'mycluster':
- Read/Write Connections: localhost:64460
- Read/Only Connections: localhost:64470で、 myrouter/ が出来た。
--force なくしてもう一回。
# mysqlrouter --bootstrap localhost:3310 --user=mysql --directory myrouter同じメッセージで同じ状態。 その前が出来なかった理由がよくわからない。
masayuki14
commented
6 years ago --name オプションがあかんかったんやろか。
まぁおいといて、次へ進む。
masayuki14
commented
6 years ago ルーター起動。
# myrouter/start.sh
# Error: PID file /work/myrouter/mysqlrouter.pid found. Already running?すでにうごいてる？
masayuki14
commented
6 years ago # ps aux | grep route[r]
root 11079 0.0 0.3 51420 3776 pts/2 S 02:24 0:00 sudo ROUTER_PID=/work/myrouter/mysqlrouter.pid /usr/local/mysqlrouter/bin/mysqlrouter -c /work/myrouter/mysqlrouter.conf --user=mysql
mysql 11083 0.0 0.7 719892 8120 pts/2 Sl 02:24 0:00 /usr/local/mysqlrouter/bin/mysqlrouter -c /work/myrouter/mysqlrouter.conf --user=mysql確かにぷろせすあるなぁ。
masayuki14
commented
6 years ago どうやって消すか。
# cat myrouter/stop.sh
if [ -f /work/myrouter/mysqlrouter.pid ]; then
kill -HUP `cat /work/myrouter/mysqlrouter.pid`
rm -f /work/myrouter/mysqlrouter.pid
fiシンプルだった。
masayuki14
commented
6 years ago # ./myrouter/stop.sh
Failed to execute process './myrouter/stop.sh'. Reason:
exec: Exec format error
The file './myrouter/stop.sh' is marked as an executable but could not be run by the operating system.プロセスIDとか見るとちゃんと止まりそうだが。。。
masayuki14
commented
6 years ago 実行出来ないっていうエラーメッセージ。
よくみると stop.sh には shebang の指定がない。
# bash myrouter/stop.shで止まってくれた。 これフィードバックポイントなのでは。
masayuki14
commented
6 years ago 調べると start.sh には shebang があるので漏れてる。プルリクチャンス！
masayuki14
commented
6 years ago ちょっとしらべたけど contribute する前の手続きが結構めんどい 😓
masayuki14
commented
6 years ago 気を取り直して続ける。
MySQL InnoDB Cluster を試してみる。
公式のチュートリアル。 https://dev.mysql.com/doc/refman/5.7/en/mysql-innodb-cluster-sandbox-deployment.html
セミナーの時の公開資料 https://www.mysql.com/jp/why-mysql/presentations/mysql-innodb-cluster-201803/