[root@jh-my001 ~]# mysqlsh
mysqlsh: /lib64/libcrypto.so.10: version `OPENSSL_1.0.2' not found (required by /usr/bin/../lib/mysqlsh/libssh.so.4)
[root@jh-my001 ~]# yum -y install openssl
[root@jh-my001 ~]# mysqlsh
MySQL Shell 8.0.28
Copyright (c) 2016, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
MySQL JS >
## 내 db에 접속
MySQL JS > \c root@(/tmp/mysql.sock)
Creating a session to 'root@/tmp%2Fmysql.sock'
Please provide the password for 'root@/tmp%2Fmysql.sock': ****
Save password for 'root@/tmp%2Fmysql.sock'? [Y]es/[N]o/Ne[v]er (default No): Y => password지정할건지 묻는다. 테스트라 y함. 운영에서 저장시 보안사고!
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 13
Server version: 8.0.29-commercial MySQL Enterprise Server - Commercial
No default schema selected; type \use <schema> to set one.
MySQL localhost JS > \sql =>sql모드로 변경
Switching to SQL mode... Commands end with ;
MySQL localhost SQL > \py => python모드로 변경
Switching to Python mode...
MySQL localhost Py > \status
MySQL Shell version 8.0.28
Connection Id: 13
Current schema:
Current user: root@localhost
SSL: Not in use.
Using delimiter: ;
Server version: 8.0.29-commercial MySQL Enterprise Server - Commercial
Protocol version: Classic 10
Client library: 8.0.28
Connection: Localhost via UNIX socket
Unix socket: /tmp/mysql.sock
Server characterset: utf8mb4
Schema characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
Result characterset: utf8mb4
Compression: Disabled
Uptime: 1 hour 44 min 1.0000 sec
Threads: 2 Questions: 14 Slow queries: 0 Opens: 148 Flush tables: 3 Open tables: 64 Queries per second avg: 0.002
## Instance 설정
MySQL localhost JS > dba.configureInstance('root@(/tmp/mysql.sock)')
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...
This instance reports its own address as jh-my001:3306 => member들 통신 주소
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
ERROR: User 'root' can only connect from 'localhost'. New account(s) with proper source address specification to allow remote connection from all instances must be created to manage the cluster.
1) Create remotely usable account for 'root' with same grants and password => root권한 유저 만들건지
2) Create a new admin account for InnoDB cluster with minimal required grants => 최소권한 유저 만들건지
3) Ignore and continue
4) Cancel
Please select an option [1]: 2
Please provide an account name (e.g: icroot@%) to have it created with the necessary
privileges or leave empty and press Enter to cancel.
Account Name: icadmin
Password for new account: *
Confirm password: *
applierWorkerThreads will be set to the default value of 4.
NOTE: Some configuration options need to be fixed: => 현재 conf를 읽고 고쳐야하는 것드를 보여준다.
+----------------------------------------+---------------+----------------+--------------------------------------------------+
| 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 |
+----------------------------------------+---------------+----------------+--------------------------------------------------+
Some variables need to be changed, but cannot be done dynamically on the server.
Do you want to perform the required configuration changes? [y/n]: y => parameter 수정해줌.(5.7은 my.cnf에 넣어주는데 8.0은 안넣어주고 다른곳에 넣어진다.-> datadir의 mysql-auto.cnf)
Do you want to restart the instance after configuring it? [y/n]: y => 서버 재시작 여부(mysql_safe로 기동했을 시 자동 restart 가능하다, 하지만 mysqld로만 띄우면 직접 다시 띄어야한다)
Cluster admin user 'icadmin'@'%' created.
Configuring instance...
The instance 'jh-my001:3306' was configured to be used in an InnoDB cluster.
Restarting MySQL...
NOTE: MySQL server at jh-my001:3306 was restarted.
MySQL localhost JS > Restarting mysqld...
2022-05-09T07:34:51.336934Z mysqld_safe Number of processes running now: 0
2022-05-09T07:34:51.340487Z mysqld_safe mysqld restarted
## 재기동
[root@jh-my001 ~]# mysqld --user=mysql &
## 생성한 icamin 유저의 권한 확인
mysql> show grants for 'icadmin'@'%';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for icadmin@% |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, RELOAD, SHUTDOWN, PROCESS, FILE, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO `icadmin`@`%` WITH GRANT OPTION |
| GRANT CLONE_ADMIN,CONNECTION_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,ROLE_ADMIN,SYSTEM_VARIABLES_ADMIN ON *.* TO `icadmin`@`%` WITH GRANT OPTION |
| GRANT INSERT, UPDATE, DELETE ON `mysql`.* TO `icadmin`@`%` WITH GRANT OPTION |
| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_innodb_cluster_metadata`.* TO `icadmin`@`%` WITH GRANT OPTION |
| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_innodb_cluster_metadata_bkp`.* TO `icadmin`@`%` WITH GRANT OPTION |
| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_innodb_cluster_metadata_previous`.* TO `icadmin`@`%` WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
## auto로 적용된 config 조회
[root@jh-my001 data]# cat /data/data/mysqld-auto.cnf
{"Version": 2, "mysql_static_variables": {"gtid_mode": {"Value": "ON", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1652060770050232}}, "slave_parallel_workers": {"Value": "4", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1652062713406968}}, "enforce_gtid_consistency": {"Value": "ON", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1652060770048914}}, "replica_parallel_workers": {"Value": "4", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1652062713406968}}}, "mysql_dynamic_variables": {"binlog_transaction_dependency_tracking": {"Value": "WRITESET", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1652060770048658}}}}
