mysql> SET SQL_LOG_BIN=0; CREATE USER repl@'%' IDENTIFIED BY '12345678'; GRANT REPLICATION SLAVE ON *.* TO repl@'%'; FLUSH PRIVILEGES; SET SQL_LOG_BIN=1; CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='12345678' FOR CHANNEL 'group_replication_recovery'; #安装组复制插件 INSTALL PLUGIN group_replication SONAME 'group_replication.so'; #查看插件是否安装成功 SHOW PLUGINS;
插件安装完成之后，将配置文件注释的那两行#去掉，重启数据库，并启动组复制进程：
1 2 3 4 5 6
#启动组复制 SET GLOBAL group_replication_bootstrap_group=ON; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_group=OFF; #查看组成员 SELECT * FROM performance_schema.replication_group_members;
mysql> CREATE DATABASE test1; USE test; CREATE TABLE t4 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL); INSERT INTO t4 VALUES (1, 'Luis'); 检查表t1和二进制日志的内容。 SELECT * FROM t4; SHOW BINLOG EVENTS;
3、从节点2（dax-mysql-slave）执行:
1 2 3 4 5 6 7 8
SET SQL_LOG_BIN=0; CREATE USER repl@'%' IDENTIFIED BY '12345678'; GRANT REPLICATION SLAVE ON *.* TO repl@'%'; FLUSH PRIVILEGES; SET SQL_LOG_BIN=1; CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='12345678' FOR CHANNEL 'group_replication_recovery'; INSTALL PLUGIN group_replication SONAME 'group_replication.so'; show plugins;
将配置文件注释的那两行#去掉，重启数据库
1
mysql>START GROUP_REPLICATION;
4、从节点3(dax-mysql-mha)执行:
1 2 3 4 5 6 7 8
SET SQL_LOG_BIN=0; CREATE USER repl@'%' IDENTIFIED BY '12345678'; GRANT REPLICATION SLAVE ON *.* TO repl@'%'; FLUSH PRIVILEGES; SET SQL_LOG_BIN=1; CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='12345678' FOR CHANNEL 'group_replication_recovery'; INSTALL PLUGIN group_replication SONAME 'group_replication.so'; show plugins;
MySQL Shell用来配置服务器以供InnoDB集群使用的供应脚本需要访问Python版本2.7。对于沙箱部署，在用于部署的单台机器上需要Python，生产部署需要在每个服务器实例上使用Python。
1 2
tar -zxvf mysql-shell-8.0.11-linux-glibc2.12-x86-64bit.tar.gz mv mysql-shell-8.0.11-linux-glibc2.12-x86-64bit mysql-shell
主节点1（dax-mysql-master),连接主节点,授权repl用户集群管理权限
1 2 3 4
GRANT ALL PRIVILEGES ON mysql_innodb_cluster_metadata.* TO repl@'%' identified by '12345678' WITH GRANT OPTION; GRANT RELOAD, SHUTDOWN, PROCESS, FILE, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO repl@'%' identified by '12345678' WITH GRANT OPTION; GRANT SELECT ON *.* TO repl@'%' identified by '12345678' WITH GRANT OPTION; flush privileges;
SELECT * FROM performance_schema.replication_group_members;
查看主节点
1
SHOW STATUS LIKE 'group_replication_primary_member';
或者使用如下方法
1 2
set global show_compatibility_56 = on; select MEMBER_HOST from performance_schema.replication_group_members where MEMBER_ID=(select VARIABLE_VALUE from information_schema.global_status where variable_name='GROUP_REPLICATION_PRIMARY_MEMBER');
如果AUDIT_TRAIL参数设置为xml, or xml,extended，审计记录会以xml的格式存储。如果AUDIT_SYS_OPERATIONS参数启用，用于审计sys用户的记录，审计信息也会强制写入这个位置。 在多租户容器数据库，audit_file_dest的值设置范围是cdb。虽然审计记录是每个pdb提供的，但是每个pdb不能修改该初始化参数。
root@db 09:21: [(none)]> grant select,update,delete,insert on *.* to 'clevergo'@'%' identified by '123123'; ERROR 1805 (HY000): Column count of mysql.user is wrong. Expected 45, found 43. The table is probably corrupted
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure. Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. Checking system database. mysql.columns_priv OK mysql.db OK mysql.engine_cost OK mysql.event OK mysql.func OK mysql.general_log OK mysql.gtid_executed OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.server_cost OK mysql.servers OK mysql.slave_master_info OK mysql.slave_relay_log_info OK mysql.slave_worker_info OK mysql.slow_log OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK The sys schema is already up to date (version 1.5.1). Found 0 sys functions, but expected 22. Re-installing the sys schema. Upgrading the sys schema. Checking databases. t.t_test OK Upgrade process completed successfully. Checking if update is needed.
3、进入mysql，再次执行创建用户：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
root@db 09:26: [(none)]> grant select,update,delete,insert on *.* to 'clevergo'@'%' identified by '123123'; Query OK, 0 rows affected, 1 warning (0.01 sec)
Traceback (most recent call last): File "./mysqldbcompare.py", line 28, in <module> from mysql.utilities.common.tools import check_python_version ImportError: No module named utilities.common.tools
# WARNING: Using a password on the command line interface can be insecure. # server1 on 10.0.7.50: ... connected. # server2 on 10.0.7.51: ... connected. # Checking databases test on server1 and test on server2 # ERROR: The list of objects differs among database test and test.
# WARNING: Using a password on the command line interface can be insecure. # server1 on 10.0.7.50: ... connected. # server2 on 10.0.7.51: ... connected. # Comparing `test1` to `test1` [PASS] # Comparing `test1`.`aa` to `test1`.`aa` [PASS] # Comparing `test1`.`test` to `test1`.`test` [PASS] # Success. All objects are the same.
# WARNING: Using a password on the command line interface can be insecure. # server1 on 10.0.7.50: ... connected. # server2 on 10.0.7.51: ... connected. # Comparing test1.test to test1.test [PASS] # Success. All objects are the same.
# WARNING: Using a password on the command line interface can be insecure. # server1 on 10.0.7.50: ... connected. # server2 on 10.0.7.51: ... connected. # Checking databases test1 on server1 and test1 on server2 # # Defn Row Data # Type Object Name Diff Count Check # ------------------------------------------------------------------------- # TABLE aa pass pass - # - Compare table checksum pass # TABLE test pass FAIL ERROR: Row counts are not the same among `test1`.`test` and `test1`.`test`. #
提示test1库下面的test表数据不一致。
8、创建测试数据，表结构不同，数据内容相同：
server1：
1 2 3 4 5 6 7 8 9 10 11 12
create table test1 (id int(10) primary key); insert into test1 values (1),(2),(3),(4),(5); select * from test1; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | +----+
server2:
1 2 3 4 5 6 7 8 9 10 11 12
create table test1 (id bigint(20)); insert into test1 values (1),(2),(3),(4),(5); root@db 06:28: [test1]> select * from test1; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+
# WARNING: Using a password on the command line interface can be insecure. # server1 on 10.0.7.50: ... connected. # server2 on 10.0.7.51: ... connected. # Comparing test1.test1 to test1.test1 [FAIL] # Object definitions differ. (--changes-for=server1) #
--- test1.test1 +++ test1.test1 @@ -1,4 +1,3 @@ CREATE TABLE `test1` ( - `id` int(10) NOT NULL, - PRIMARY KEY (`id`) + `id` bigint(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 # Compare failed. One or more differences found.
# WARNING: Using a password on the command line interface can be insecure. # server1 on 10.0.7.50: ... connected. # server2 on 10.0.7.51: ... connected. # Checking databases test1 on server1 and test1 on server2 # # Defn Row Data # Type Object Name Diff Count Check # ------------------------------------------------------------------------- # TABLE aa pass pass - # - Compare table checksum pass # TABLE test pass FAIL ERROR: Row counts are not the same among `test1`.`test` and `test1`.`test`. #
当检测到异常之后会直接退出，不进行下面的比较，因此未检测到test1表的内容，加上-t（-t, –run-all-tests do not abort when a diff test fails）参数，运行测试模式，遇到异常之后仍然执行下面的比较： ./mysqldbcompare.py –server1=root:12345678@10.0.7.50:3306 –server2=root:12345678@10.0.7.51:3306 test1 –changes-for=server1 –difftype=sql -t
# WARNING: Using a password on the command line interface can be insecure. # server1 on 10.0.7.50: ... connected. # server2 on 10.0.7.51: ... connected. # Checking databases test1 on server1 and test1 on server2 # # Defn Row Data # Type Object Name Diff Count Check # ------------------------------------------------------------------------- # TABLE aa pass pass - # - Compare table checksum pass # TABLE test pass FAIL - # - Compare table checksum FAIL # - Find row differences FAIL # # Row counts are not the same among `test1`.`test` and `test1`.`test`. # # Transformation for --changes-for=server1: #
# WARNING: Using a password on the command line interface can be insecure. # server1 on 10.0.7.50: ... connected. # server2 on 10.0.7.51: ... connected. # WARNING: Objects in server1.test but not in server2.test: # TABLE: gtid_test14 # Compare failed. One or more differences found.
# WARNING: Using a password on the command line interface can be insecure. # server1 on 10.0.7.50: ... connected. # server2 on 10.0.7.51: ... connected. # Checking databases test on server1 and test on server2 # ERROR: The list of objects differs among database test and test.
cd /data/soft wget https://dl.eff.org/certbot-auto chmod a+x certbot-auto
2、生成证书
/data/soft/certbot-auto –apache certonly
1 2 3 4 5
Saving debug log to /var/log/letsencrypt/letsencrypt.log Could not choose appropriate plugin: The apache plugin is not working; there may be problems with your existing configuration. The error was: NoInstallationError('Cannot find Apache executable apachectl',) The apache plugin is not working; there may be problems with your existing configuration. The error was: NoInstallationError('Cannot find Apache executable apachectl',)
Saving debug log to /var/log/letsencrypt/letsencrypt.log Could not choose appropriate plugin: The apache plugin is not working; there may be problems with your existing configuration. The error was: NoInstallationError('Could not find configuration root',) The apache plugin is not working; there may be problems with your existing configuration. The error was: NoInstallationError('Could not find configuration root',)
Saving debug log to /var/log/letsencrypt/letsencrypt.log Plugins selected: Authenticator apache, Installer apache No names were found in your configuration files. Please enter in your domain name(s) (comma and/or space separated) (Enter 'c' to cancel): www.test.com Obtaining a new certificate Performing the following challenges: http-01 challenge for www.test.com Cleaning up challenges Unable to find a virtual host listening on port 80 which is currently needed for Certbot to prove to the CA that you control your domain. Please add a virtual host for port 80.
httpd: Syntax error on line 434 of /usr/local/apache2/conf/httpd.conf: Cannot load /usr/local/apache2/modules/mod_ssl.so into server: /usr/local/apache2/modules/mod_ssl.so: cannot open shared object file: No such file or directory
httpd: Syntax error on line 434 of /usr/local/apache2/conf/httpd.conf: Cannot load /usr/local/apache2/modules/mod_ssl.so into server: /usr/local/apache2/modules/mod_ssl.so:undefined symbol: ap_global_mutex_create
httpd: Syntax error on line 434 of /usr/local/apache2/conf/httpd.conf: Cannot load /usr/local/apache2/modules/mod_ssl.so into server: /usr/local/apache2/modules/mod_ssl.so: undefined symbol: ssl_cmd_SSLPassPhraseDialog
需要指定openssl路径，执行以下命令：
1
/usr/local/apache2/bin/apxs -a -i -c -L/usr/lib/openssl/engines/lib -c *.c -lcrypto -lssl -ldl
再次重启apache
1
httpd: Syntax error on line 434 of /usr/local/apache2/conf/httpd.conf: Cannot load /usr/local/apache2/modules/mod_ssl.so into server: /usr/local/apache2/modules/mod_ssl.so:undefined symbol: ap_global_mutex_create
