利用 systemd 管理 MySQL 单机多实例
利用 systemd 管理 MySQL 单机多实例
1 修改配置文件
[root@localhost /root]#cat /etc/my.cnf [mysql] #prompt = "\u@mysqldb \R:\m:\s [\d]> " prompt = "[\\u@\\h@\\p][\\d]>\\_" no_auto_rehash loose-skip-binary-as-hex [mysqld] basedir=/usr/local/mysql log_timestamps=SYSTEM user = mysql log_error_verbosity = 3 log-bin=binlog binlog-format=row log_slave_updates=ON binlog_checksum=CRC32 master-info-repository=TABLE relay-log-info-repository=TABLE gtid-mode=on enforce-gtid-consistency=true binlog_transaction_dependency_tracking=writeset transaction_write_set_extraction=XXHASH64 slave_parallel_type = LOGICAL_CLOCK slave_parallel_workers=4 #可以设置为逻辑CPU数量的2-4倍 sql_require_primary_key=1 slave_preserve_commit_order=1 slave_checkpoint_period=2 #mgr loose-plugin_load_add='mysql_clone.so' loose-plugin_load_add='group_replication.so' loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1" loose-group_replication_group_seeds= "127.0.0.1:33061,127.0.0.1:33071,127.0.0.1:33081,127.0.0.1:33091" loose-group_replication_start_on_boot=off loose-group_replication_bootstrap_group=off loose-group_replication_exit_state_action=READ_ONLY loose-group_replication_flow_control_mode = "DISABLED" loose-group_replication_single_primary_mode=ON [mysqld_multi] mysqld = /usr/local/mysql/bin/mysqld log = /data/GreatSQL/mysqld_multi.log mysqladmin = /usr/local/mysql/bin/mysqladmin user=root [mysqld@3306] datadir=/data/GreatSQL/mgr01 socket=/data/GreatSQL/mgr01/mysql.sock port=3306 server_id=3306 log-error=/data/GreatSQL/mgr01/error.log loose-group_replication_local_address= "127.0.0.1:33061" [mysqld@3307] datadir=/data/GreatSQL/mgr02 socket=/data/GreatSQL/mgr02/mysql.sock port=3307 server_id=3307 log-error=/data/GreatSQL/mgr02/error.log loose-group_replication_local_address= "127.0.0.1:33071" [mysqld@3308] datadir=/data/GreatSQL/mgr03 socket=/data/GreatSQL/mgr03/mysql.sock port=3308 server_id=3308 log-error=/data/GreatSQL/mgr03/error.log loose-group_replication_local_address= "127.0.0.1:33081" [mysqld@3309] datadir=/data/GreatSQL/mgr04 socket=/data/GreatSQL/mgr04/mysql.sock port=3309 server_id=3309 log-error=/data/GreatSQL/mgr04/error.log loose-group_replication_local_address= "127.0.0.1:33091"
2 修改 启动文件
[root@localhost /root]#cat /usr/lib/systemd/system/greatsql@.service [Unit] Description=GreatSQL Server Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql Type=notify TimeoutSec=0 PermissionsStartOnly=true #ExecStartPre=/usr/local/mysql/bin/mysqld_pre_systemd %I ExecStart=/usr/local/mysql/bin/mysqld --defaults-group-suffix=@%I $MYSQLD_OPTS EnvironmentFile=-/etc/sysconfig/mysql LimitNOFILE = 10000 Restart=on-failure RestartPreventExitStatus=1 Environment=MYSQLD_PARENT_PID=1 PrivateTmp=false [root@localhost /root]#ll /usr/lib/systemd/system/greatsql@.service [root@localhost /root]#systemctl daemon-reload
3 关闭，重启等操作
#3307 节点 kill 掉 3307 进程 [root@localhost /usr/local/mysql]#ps -ef|grep mysql mysql 16800 1 4 15:35 pts/0 00:11:13 /usr/local/mysql/bin/mysqld --datadir=/data/GreatSQL/mgr02 --socket=/data/GreatSQL/mgr02/mysql.sock --port=3307 --server_id=3307 --log-error=/data/GreatSQL/mgr02replication_local_address=127.0.0.1:33071 mysql 16855 1 4 15:35 pts/0 00:11:12 /usr/local/mysql/bin/mysqld --datadir=/data/GreatSQL/mgr03 --socket=/data/GreatSQL/mgr03/mysql.sock --port=3308 --server_id=3308 --log-error=/data/GreatSQL/mgr03replication_local_address=127.0.0.1:33081 mysql 17327 1 4 16:07 pts/2 00:10:16 /usr/local/mysql/bin/mysqld --datadir=/data/GreatSQL/mgr04 --socket=/data/GreatSQL/mgr04/mysql.sock --port=3309 --server_id=3309 --log-error=/data/GreatSQL/mgr04replication_local_address=127.0.0.1:33091 mysql 17580 1 4 16:21 pts/2 00:09:32 /usr/local/mysql/bin/mysqld --datadir=/data/GreatSQL/mgr01 --socket=/data/GreatSQL/mgr01/mysql.sock --port=3306 --server_id=3306 --log-error=/data/GreatSQL/mgr01replication_local_address=127.0.0.1:33061 [root@localhost /root]#systemctl start greatsql@3307 [root@localhost /root]#systemctl status greatsql@3307 ● greatsql@3307.service - GreatSQL Server Loaded: loaded (/usr/lib/systemd/system/greatsql@.service; disabled; vendor preset: disabled) Active: active (running) since Wed 2023-05-10 19:56:43 CST; 5s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Main PID: 25543 (mysqld) Status: "Server is operational" CGroup: /system.slice/system-greatsql.slice/greatsql@3307.service └─25543 /usr/local/mysql/bin/mysqld --defaults-group-suffix=@3307 May 10 19:56:41 localhost.localdomain systemd[1]: Starting GreatSQL Server... May 10 19:56:43 localhost.localdomain systemd[1]: Started GreatSQL Server. [root@localhost /root]#systemctl -l |grep greatsql greatsql@3307.service loaded active running GreatSQL Server system-greatsql.slice loaded active active system-greatsql.slice
同理其他节点
[root@localhost /opt]#sh mysqlmulti_sys.sh 3306 [sys@127.0.0.1@3306][(none)]> stop group_replication; Query OK, 0 rows affected (4.99 sec) [root@localhost /root]#kill 17580 [root@localhost /opt]#systemctl start greatsql@3306 [root@localhost /opt]#systemctl status greatsql@3306 ● greatsql@3306.service - GreatSQL Server Loaded: loaded (/usr/lib/systemd/system/greatsql@.service; disabled; vendor preset: disabled) Active: active (running) since Thu 2023-05-11 11:02:11 CST; 2s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Main PID: 70944 (mysqld) Status: "Server is operational" CGroup: /system.slice/system-greatsql.slice/greatsql@3306.service └─70944 /usr/local/mysql/bin/mysqld --defaults-group-suffix=@3306 May 11 11:02:10 localhost.localdomain systemd[1]: Starting GreatSQL Server... May 11 11:02:11 localhost.localdomain systemd[1]: Started GreatSQL Server. [root@localhost /opt]#sh mysqlmulti_sys.sh 3306 [sys@127.0.0.1@3306][(none)]> start group_replication; Query OK, 0 rows affected (3.03 sec) [sys@127.0.0.1@3306][(none)]> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 32218282-ef04-11ed-b942-005056a63376 | localhost.localdomain | 3306 | ONLINE | SECONDARY | 8.0.25 | | group_replication_applier | 627f6c88-ef04-11ed-818c-005056a63376 | localhost.localdomain | 3307 | ONLINE | PRIMARY | 8.0.25 | | group_replication_applier | 676c9de0-ef09-11ed-8ba8-005056a63376 | localhost.localdomain | 3309 | ONLINE | SECONDARY | 8.0.25 | | group_replication_applier | 69b2b717-ef04-11ed-b80f-005056a63376 | localhost.localdomain | 3308 | ONLINE | SECONDARY | 8.0.25 | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ 4 rows in set (0.00 sec) [root@localhost /root]#ps -ef|grep mysql mysql 25543 1 4 May10 ? 00:39:14 /usr/local/mysql/bin/mysqld --defaults-group-suffix=@3307 mysql 70944 1 5 11:02 ? 00:00:14 /usr/local/mysql/bin/mysqld --defaults-group-suffix=@3306 mysql 71233 1 10 11:05 ? 00:00:07 /usr/local/mysql/bin/mysqld --defaults-group-suffix=@3308 mysql 71397 1 22 11:06 ? 00:00:03 /usr/local/mysql/bin/mysqld --defaults-group-suffix=@3309 root 71451 66125 0 11:06 pts/2 00:00:00 sh mysqlmulti_sys.sh 3309 root 71452 71451 0 11:06 pts/2 00:00:00 /usr/local/mysql/bin/mysql -usys -px xxxxxx -h127.0.0.1 -P3309 root 71489 65449 0 11:06 pts/0 00:00:00 grep --color=auto mysql [root@localhost /root]#systemctl -l |grep greatsql greatsql@3306.service loaded active running GreatSQL Server greatsql@3307.service loaded active running GreatSQL Server greatsql@3308.service loaded active running GreatSQL Server greatsql@3309.service loaded active running GreatSQL Server system-greatsql.slice loaded active active system-greatsql.slice
创建 sys 账号
[root@localhostmysql.sock][(none)]> CREATE USER sys@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'repl.abc'; Query OK, 0 rows affected (0.02 sec) [root@localhostmysql.sock][(none)]> GRANT ALL PRIVILEGES ON *.* TO `sys`@`%`; Query OK, 0 rows affected (0.02 sec) [root@localhostmysql.sock][(none)]> select user,host from mysql.user; +------------------+-----------+ | user | host | +------------------+-----------+ | repl | % | | repl11 | % | | sys | % | | mysql.infoschema | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +------------------+---------
脚本登录，方便登录
[root@localhost /root]#cat /opt/mysqlmulti_sys.sh #!/bin/sh #create on 20230511 #set env MYSQL_USER=sys MYSQL_PASS='repl.abc' MYSQL_PATH=/usr/local/mysql/bin/mysql host=127.0.0.1 #MYSQL_PORT=3306 #/usr/local/mysql/bin/mysql -uroot -p -P3306 -S /data/GreatSQL/mgr01/mysql.sock #check paramater if [ $# -ne 1 ] ;then echo -e "need parameter 1,like 3306,3307,3308,3309" exit 1 else if [ $1 == 3306 ];then mysql_port=3306 mysql_sock=/data/GreatSQL/mgr01/mysql.sock elif [ $1 == 3307 ];then mysql_port=3307 mysql_sock=/data/GreatSQL/mgr02/mysql.sock elif [ $1 == 3308 ];then mysql_port=3308 mysql_sock=/data/GreatSQL/mgr03/mysql.sock elif [ $1 == 3309 ];then mysql_port=3309 mysql_sock=/data/GreatSQL/mgr04/mysql.sock else echo -e "need parameter 1,like 3306,3307,3308,3309" exit 1 fi fi ${MYSQL_PATH} -u${MYSQL_USER} -p${MYSQL_PASS} -h${host} -P${mysql_port}
登录即可
[root@localhost /opt]#sh mysqlmulti_sys.sh 3309 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.25-17 GreatSQL, Release 17, Revision 4733775f703 Copyright (c) 2021-2021 GreatDB Software Co., Ltd Copyright (c) 2009-2021 Percona LLC and/or its affiliates Copyright (c) 2000, 2021, 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 '\h' for help. Type '\c' to clear the current input statement. [sys@127.0.0.1@3309][(none)]> start group_replication; Query OK, 0 rows affected (2.62 sec) [sys@127.0.0.1@3309][(none)]> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 32218282-ef04-11ed-b942-005056a63376 | localhost.localdomain | 3306 | ONLINE | SECONDARY | 8.0.25 | | group_replication_applier | 627f6c88-ef04-11ed-818c-005056a63376 | localhost.localdomain | 3307 | ONLINE | PRIMARY | 8.0.25 | | group_replication_applier | 676c9de0-ef09-11ed-8ba8-005056a63376 | localhost.localdomain | 3309 | ONLINE | SECONDARY | 8.0.25 | | group_replication_applier | 69b2b717-ef04-11ed-b80f-005056a63376 | localhost.localdomain | 3308 | ONLINE | SECONDARY | 8.0.25 | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ 4 rows in set (0.00 sec)
