云计算运维

Windows Server 2003 - Windows Server 2019 系统工具,Liunx系统脚本,Mysql、Nginx、PHP、Redis、K8S、Seafile、Weblogic 、Jenkins、DNS、DHCP、FTP、IIS、Zookeeper、Rabbitmq、Oracle、Tomcat、Mavrn等服务搭建维护,请关注我.

mysql数据备份与恢复和mysql多实例部署


1.mysql数据库备份与恢复
1.1 数据库常用备份方案
数据库备份方案:

冷备份:先把数据库服务停掉,然后拷贝数据库目录下的文件进行备份 物理备份
温备份:
热备份:数据库服务正常运行情况,直接对数据库进行备份
全量备份:全部备份
增量备份:第一次全备,第二次在第一次全备更改的基础上备份
差异备份:全部备份,差异备份-->全备
备份方案 特点
全量备份 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。数据恢复快。备份时间长
增量备份 增量备份是指在一次全备份或上一次增量备份后,以后每次的备份只需备份与前一次相比增加和者被修改的文件。这就意味着,第一次增量备份的对象是进行全备后所产生的增加和修改的文件;第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件,如此类推。没有重复的备份数据,备份时间短,恢复数据时必须按一定的顺序进行
差异备份 备份上一次的完全备份后发生变化的所有文件。差异备份是指在一次全备份后到进行差异备份的这段时间内,对那些增加或者修改文件的备份。在进行恢复时,我们只需对第一次全量备份和最后一次差异备份进行恢复。
1.2 mysql备份工具mysqldump
//语法:
mysqldump [OPTIONS] database [tables ...]//备份表(多表之间用空格连接)
mysqldump [OPTIONS] --all-databases [OPTIONS]//全备
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]//备份指定的数据库

//常用的OPTIONS:
-uUSERNAME //指定数据库用户名
-hHOST //指定服务器主机,请使用ip地址
-pPASSWORD //指定数据库用户的密码
-P# //指定数据库监听的端口,这里的#需用实际的端口号代替,如-P3307

    [root@mr ~]# mysqldump -uroot -pmarui runtime tb_course > tb_course-$(date '+%Y%m%d%H%M%S').sql  (备份表)
    mysqldump: [Warning] Using a password on the command line interface can be insecure.
    [root@mr ~]# ls
    
    tb_course-20220731140014.sql
    
    [root@mr ~]# vim .my.cnf(免密登录)
    [mysqldump]
    user=root
    password=marui
    (实现免密登录)
    [root@mr ~]# mysqldump runtime tb_course > tb_course-$(date '+%Y%m%d%H%M%S').sql
    [root@mr ~]# ls
    
    tb_course-20220731140014.sql
    tb_course-20220731140726.sql
    
    [root@mr ~]# mysqldump --databases runtime > runtime-$(date '+%Y%m%d%H%M%S').sql(备份数据库)
    [root@mr ~]# ls
    
    runtime-20220731141008.sql
    
    [root@mr ~]# mysqldump --all-databases > all-$(date '+%Y%m%d%H%M%S').sql(全备)
    [root@mr ~]# ls
    
    all-20220731141206.sql
    
    [root@mr ~]# 

1.3 mysql数据恢复

    mysql> delete from tb_course where id = 5 or id = 6;(删除数据)
    Query OK, 2 rows affected (0.01 sec)
    
    mysql> select * from tb_course;
    +----+-------------+
    | id | course_name |
    +----+-------------+
    |  1 | Java        |
    |  2 | Mysql       |
    |  3 | Python      |
    |  4 | Go          |
    +----+-------------+
    4 rows in set (0.00 sec)
    
    mysql> 
    [root@mr ~]# vim .my.cnf
    [mysqldump]
    user=root
    password=marui
    
    [client]
    user=root
    password=marui
    方法一:
    [root@mr ~]# mysql runtime < tb_course-20220731140726.sql
    
    mysql> show tables;
    +-------------------+
    | Tables_in_runtime |
    +-------------------+
    | tb_course         |
    | tb_students_info  |
    +-------------------+
    2 rows in set (0.00 sec)
    
    mysql> select * from tb_course;
    +----+-------------+
    | id | course_name |
    +----+-------------+
    |  1 | Java        |
    |  2 | Mysql       |
    |  3 | Python      |
    |  4 | Go          |
    |  5 | C++         |
    |  6 | HTML        |
    +----+-------------+
    6 rows in set (0.00 sec)
    
    mysql> 
    方法二:
    mysql> source tb_course-20220731140726.sql
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    ......
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from tb_course;
    +----+-------------+
    | id | course_name |
    +----+-------------+
    |  1 | Java        |
    |  2 | Mysql       |
    |  3 | Python      |
    |  4 | Go          |
    |  5 | C++         |
    |  6 | HTML        |
    +----+-------------+
    6 rows in set (0.00 sec)
    
    mysql> 

物理备份

    [root@mr ~]# cd /opt/data/
    [root@mr data]# ls
    auto.cnf         client-key.pem  ib_logfile1  mysql_bin.000003  performance_schema  server-cert.pem
    ca-key.pem       ib_buffer_pool  ibtmp1       mysql_bin.000004  private_key.pem     server-key.pem
    ca.pem           ibdata1         mr.err       mysql_bin.index   public_key.pem      sys
    client-cert.pem  ib_logfile0     mysql        mysql.pid         runtime
    [root@mr data]# mkdir /opt/mysql_bak/
    [root@mr data]# mv * /opt/mysql_bak/
    [root@mr data]# ls
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    +--------------------+
    1 row in set (0.00 sec)
    
    mysql> 
    [root@mr data]# mv /opt/mysql_bak/* .
    [root@mr data]# ls
    auto.cnf         client-key.pem  ib_logfile1  mysql_bin.000003  performance_schema  server-cert.pem
    ca-key.pem       ib_buffer_pool  ibtmp1       mysql_bin.000004  private_key.pem     server-key.pem
    ca.pem           ibdata1         mr.err       mysql_bin.index   public_key.pem      sys
    client-cert.pem  ib_logfile0     mysql        mysql.pid         runtime
    [root@mr data]#
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | runtime            |
    | sys                |
    +--------------------+
    5 rows in set (0.00 sec)
    
    mysql> 

1.4 差异备份

    [root@mr ~]# vim /etc/my.cnf
    [mysqld]
    basedir = /usr/local/mysql
    datadir = /opt/data
    socket = /tmp/mysql.sock
    port = 3306
    pid-file = /opt/data/mysql.pid
    user = mysql
    skip-name-resolve
    sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    server-id=10
    log-bin=mysql_bin
    [root@mr ~]# systemctl restart mysqld
    [root@mr ~]# mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all.chayi.sql
    (--single-transaction是事务日志,--flush-logs是刷新日志,--master-data=2指定master标志符,--delete-master-logs删除主的日志)
    [root@mr ~]# ls
    
    all.chayi.sql
    
    [root@mr ~]# 
    mysql> insert tb_course(course_name) values('English'),('math');
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> delete from tb_course where id = 4;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from tb_course;
    +----+-------------+
    | id | course_name |
    +----+-------------+
    |  1 | Java        |
    |  2 | Mysql       |
    |  3 | Python      |
    |  5 | C++         |
    |  6 | HTML        |
    |  7 | English     |
    |  8 | math        |
    +----+-------------+
    7 rows in set (0.00 sec)
    
    mysql> 

刷新二进制日志

    [root@mr ~]# ll /opt/data/
    total 123060
    
    -rw-r-----. 1 mysql mysql      713 Jul 31 16:04 mysql_bin.000003
    -rw-r-----. 1 mysql mysql       19 Jul 31 15:59 mysql_bin.index
    
    drwxr-x---. 2 mysql mysql     8192 Jul 27 16:21 sys
    [root@mr ~]# cat /opt/data/mysql_bin.index 
    ./mysql_bin.000003
    [root@mr ~]# mysqladmin -uroot -pmarui flush-logs
    mysqladmin: [Warning] Using a password on the command line interface can be insecure.
    [root@mr ~]# ll /opt/data/
    total 123064
    
    drwxr-x---. 2 mysql mysql     4096 Jul 27 16:21 mysql
    -rw-r-----. 1 mysql mysql      760 Jul 31 16:09 mysql_bin.000003
    -rw-r-----. 1 mysql mysql      154 Jul 31 16:09 mysql_bin.000004
    -rw-r-----. 1 mysql mysql       38 Jul 31 16:09 mysql_bin.index
    
    [root@mr ~]# cat /opt/data/mysql_bin.index 
    ./mysql_bin.000003
    ./mysql_bin.000004
    [root@mr ~]# 
    [root@mr ~]# mysql < all.chayi.sql
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | runtime            |
    | sys                |
    +--------------------+
    5 rows in set (0.00 sec)
    
    mysql> use runtime;
    Database changed
    mysql> select * from tb_course;
    +----+-------------+
    | id | course_name |
    +----+-------------+
    |  1 | Java        |
    |  2 | Mysql       |
    |  3 | Python      |
    |  4 | Go          |
    |  5 | C++         |
    |  6 | HTML        |
    +----+-------------+
    6 rows in set (0.00 sec)
    
    mysql> 
    mysql> show binlog events in 'mysql_bin.000003';
    +------------------+-----+----------------+-----------+-------------+---------------------------------------+
    | Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
    +------------------+-----+----------------+-----------+-------------+---------------------------------------+
    | mysql_bin.000003 |   4 | Format_desc    |        10 |         123 | Server ver: 5.7.38-log, Binlog ver: 4 |
    | mysql_bin.000003 | 123 | Previous_gtids |        10 |         154 |                                       |
    | mysql_bin.000003 | 154 | Anonymous_Gtid |        10 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
    | mysql_bin.000003 | 219 | Query          |        10 |         294 | BEGIN                                 |
    | mysql_bin.000003 | 294 | Table_map      |        10 |         352 | table_id: 140 (runtime.tb_course)     |
    | mysql_bin.000003 | 352 | Write_rows     |        10 |         410 | table_id: 140 flags: STMT_END_F       |
    | mysql_bin.000003 | 410 | Xid            |        10 |         441 | COMMIT /* xid=480 */                  |
    | mysql_bin.000003 | 441 | Anonymous_Gtid |        10 |         506 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
    | mysql_bin.000003 | 506 | Query          |        10 |         581 | BEGIN                                 |
    | mysql_bin.000003 | 581 | Table_map      |        10 |         639 | table_id: 140 (runtime.tb_course)     |
    | mysql_bin.000003 | 639 | Delete_rows    |        10 |         682 | table_id: 140 flags: STMT_END_F       |
    | mysql_bin.000003 | 682 | Xid            |        10 |         713 | COMMIT /* xid=482 */                  |
    | mysql_bin.000003 | 713 | Rotate         |        10 |         760 | mysql_bin.000004;pos=4                |
    +------------------+-----+----------------+-----------+-------------+---------------------------------------+
    13 rows in set (0.00 sec)
    
    mysql> 
    [root@mr ~]# mysqlbinlog --stop-position=682 /opt/data/mysql_bin.000003 | mysql -uroot -pmarui
    mysql: [Warning] Using a password on the command line interface can be insecure.
    [root@mr ~]# 
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | runtime            |
    | sys                |
    +--------------------+
    5 rows in set (0.01 sec)
    
    mysql> use runtime;
    Database changed
    mysql> select * from tb_course;
    +----+-------------+
    | id | course_name |
    +----+-------------+
    |  1 | Java        |
    |  2 | Mysql       |
    |  3 | Python      |
    |  4 | Go          |
    |  5 | C++         |
    |  6 | HTML        |
    |  7 | English     |
    |  8 | math        |
    +----+-------------+
    8 rows in set (0.00 sec)
    
    mysql> 

2 mysql多实例部署
1 软件下载

    [root@mr ~]# cd /usr/src/
    [root@mr src]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz

2 配置用户和组并解压二进制程序至/usr/local下

    [root@mr src]# id mysql
    id: ‘mysql’: no such user
    [root@mr src]# useradd -r -M -s /sbin/nologin mysql
    [root@mr src]# ls
    debug  kernels  mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
    [root@mr src]# tar xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
    [root@mr src]# cd /usr/local/
    [root@mr local]# ll
    total 0
    drwxr-xr-x. 2 root root   6 May 19  2020 bin
    drwxr-xr-x. 2 root root   6 May 19  2020 etc
    drwxr-xr-x. 2 root root   6 May 19  2020 games
    drwxr-xr-x. 2 root root   6 May 19  2020 include
    drwxr-xr-x. 2 root root   6 May 19  2020 lib
    drwxr-xr-x. 3 root root  17 Jul  6 09:33 lib64
    drwxr-xr-x. 2 root root   6 May 19  2020 libexec
    drwxr-xr-x. 9 root root 129 Jul 31 17:26 mysql-5.7.38-linux-glibc2.12-x86_64
    drwxr-xr-x. 2 root root   6 May 19  2020 sbin
    drwxr-xr-x. 5 root root  49 Jul  6 09:33 share
    drwxr-xr-x. 2 root root   6 May 19  2020 src
    [root@mr local]# 
    [root@mr local]# ln -s mysql-5.7.38-linux-glibc2.12-x86_64 mysql
    [root@mr local]# ll
    total 0
    drwxr-xr-x. 2 root root   6 May 19  2020 bin
    drwxr-xr-x. 2 root root   6 May 19  2020 etc
    drwxr-xr-x. 2 root root   6 May 19  2020 games
    drwxr-xr-x. 2 root root   6 May 19  2020 include
    drwxr-xr-x. 2 root root   6 May 19  2020 lib
    drwxr-xr-x. 3 root root  17 Jul  6 09:33 lib64
    drwxr-xr-x. 2 root root   6 May 19  2020 libexec
    lrwxrwxrwx. 1 root root  35 Jul 31 17:27 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64
    drwxr-xr-x. 9 root root 129 Jul 31 17:26 mysql-5.7.38-linux-glibc2.12-x86_64
    drwxr-xr-x. 2 root root   6 May 19  2020 sbin
    drwxr-xr-x. 5 root root  49 Jul  6 09:33 share
    drwxr-xr-x. 2 root root   6 May 19  2020 src
    [root@mr local]# 
    [root@mr local]# chown -R mysql.mysql mysql*
    [root@mr local]# ll
    total 0
    drwxr-xr-x. 2 root  root    6 May 19  2020 bin
    drwxr-xr-x. 2 root  root    6 May 19  2020 etc
    drwxr-xr-x. 2 root  root    6 May 19  2020 games
    drwxr-xr-x. 2 root  root    6 May 19  2020 include
    drwxr-xr-x. 2 root  root    6 May 19  2020 lib
    drwxr-xr-x. 3 root  root   17 Jul  6 09:33 lib64
    drwxr-xr-x. 2 root  root    6 May 19  2020 libexec
    lrwxrwxrwx. 1 mysql mysql  35 Jul 31 17:27 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64
    drwxr-xr-x. 9 mysql mysql 129 Jul 31 17:26 mysql-5.7.38-linux-glibc2.12-x86_64
    drwxr-xr-x. 2 root  root    6 May 19  2020 sbin
    drwxr-xr-x. 5 root  root   49 Jul  6 09:33 share
    drwxr-xr-x. 2 root  root    6 May 19  2020 src
    [root@mr local]# pwd
    /usr/local
    [root@mr local]# ls
    bin  etc  games  include  lib  lib64  libexec  mysql  mysql-5.7.38-linux-glibc2.12-x86_64  sbin  share  src
    [root@mr local]# cd mysql
    [root@mr mysql]# ls
    bin  docs  include  lib  LICENSE  man  README  share  support-files
    [root@mr mysql]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
    [root@mr mysql]# source /etc/profile.d/mysql.sh 
    [root@mr mysql]# ln -s /usr/local/mysql/include/ /usr/include/mysql
    [root@mr mysql]# chown -R mysql.mysql /usr/include/mysql
    [root@mr mysql]# ll -d /usr/include/mysql
    lrwxrwxrwx. 1 mysql mysql 25 Jul 31 17:31 /usr/include/mysql -> /usr/local/mysql/include/
    [root@mr mysql]# 
    [root@mr mysql]# vim /etc/ld.so.conf.d/mysql.conf
    
    /usr/local/mysql/lib
    [root@mr mysql]# vim /etc/man_db.conf 
    
    MANDATORY_MANPATH                       /usr/man
    MANDATORY_MANPATH                       /usr/share/man
    MANDATORY_MANPATH                       /usr/local/share/man
    MANDATORY_MANPATH                       /usr/local/mysql/man

3 创建各实例数据存放的目录

    [root@mr ~]# mkdir -p /opt/data/{3306,3307,3308}
    [root@mr ~]# chown -R mysql.mysql /opt/data/
    [root@mr ~]# ll -d /opt/data/
    drwxr-xr-x. 5 mysql mysql 42 Jul 31 17:40 /opt/data/
    [root@mr ~]# tree /opt/data/
    /opt/data/
    ├── 3306
    ├── 3307
    └── 3308
    
    3 directories, 0 files
    [root@mr ~]# 

4 初始化各实例

    [root@mr ~]# mysqld --initialize --user mysql --datadir /opt/data/3306
    2022-07-31T09:44:28.907046Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
    2022-07-31T09:44:29.040275Z 0 [Warning] InnoDB: New log files created, LSN=45790
    2022-07-31T09:44:29.064827Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
    2022-07-31T09:44:29.068832Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 5f337cfb-10b5-11ed-8041-000c2950070e.
    2022-07-31T09:44:29.069409Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
    2022-07-31T09:44:29.481826Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
    2022-07-31T09:44:29.481870Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
    2022-07-31T09:44:29.482251Z 0 [Warning] CA certificate ca.pem is self signed.
    2022-07-31T09:44:29.507332Z 1 [Note] A temporary password is generated for root@localhost: ds=WqMxEw5B%
    [root@mr ~]# echo 'ds=WqMxEw5B%' > 3306
    [root@mr ~]# cat 3306
    ds=WqMxEw5B%
    [root@mr ~]# 
    [root@mr ~]# mysqld --initialize --user mysql --datadir /opt/data/3307
    2022-07-31T09:45:57.115760Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
    2022-07-31T09:45:57.253402Z 0 [Warning] InnoDB: New log files created, LSN=45790
    2022-07-31T09:45:57.274926Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
    2022-07-31T09:45:57.279112Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 93c74f09-10b5-11ed-844a-000c2950070e.
    2022-07-31T09:45:57.280633Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
    2022-07-31T09:45:57.576351Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
    2022-07-31T09:45:57.576387Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
    2022-07-31T09:45:57.576764Z 0 [Warning] CA certificate ca.pem is self signed.
    2022-07-31T09:45:57.596717Z 1 [Note] A temporary password is generated for root@localhost: LzXC#WG-x1Kd
    [root@mr ~]# echo 'LzXC#WG-x1Kd' > 3307
    [root@mr ~]# cat 3307
    LzXC#WG-x1Kd
    [root@mr ~]# mysqld --initialize --user mysql --datadir /opt/data/3308
    2022-07-31T09:46:48.315446Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
    2022-07-31T09:46:48.483622Z 0 [Warning] InnoDB: New log files created, LSN=45790
    2022-07-31T09:46:48.509017Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
    2022-07-31T09:46:48.517038Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: b251987e-10b5-11ed-8604-000c2950070e.
    2022-07-31T09:46:48.517584Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
    2022-07-31T09:46:48.708884Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
    2022-07-31T09:46:48.708917Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
    2022-07-31T09:46:48.709402Z 0 [Warning] CA certificate ca.pem is self signed.
    2022-07-31T09:46:48.811564Z 1 [Note] A temporary password is generated for root@localhost: ,L&rJy+Sm9z:
    [root@mr ~]# echo ',L&rJy+Sm9z:' > 3308
    [root@mr ~]# cat 3308
    ,L&rJy+Sm9z:
    [root@mr ~]# 
    [root@mr ~]# ls
    3306  3307  3308  anaconda-ks.cfg

5 安装perl

    [root@mr ~]# yum -y install perl
    ......
      python3-rpm-macros-3-42.el8.noarch                                                                            
      qt5-srpm-macros-5.15.3-1.el8.noarch                                                                           
      redhat-rpm-config-125-1.el8.noarch                                                                            
      rust-srpm-macros-5-2.el8.noarch                                                                               
      systemtap-sdt-devel-4.7-1.el8.x86_64                                                                          
      unzip-6.0-46.el8.x86_64                                                                                       
      zip-3.0-23.el8.x86_64                                                                                         
    
    Complete!
    [root@mr ~]# 

配置配置文件/etc/my.cnf

    [root@mr ~]# vim /etc/my.cnf
    [mysqld_multi]
    mysqld = /usr/local/mysql/bin/mysqld_safe
    mysqladmin = /usr/local/mysql/bin/mysqladmin
    
    [mysqld3306]
    datadir = /opt/data/3306
    port = 3306
    socket = /tmp/mysql3306.sock
    pid-file = /opt/data/3306/mysql_3306.pid
    log-error=/var/log/3306.log
    
    [mysqld3307]
    datadir = /opt/data/3307
    port = 3307
    socket = /tmp/mysql3307.sock
    pid-file = /opt/data/3307/mysql_3307.pid
    log-error=/var/log/3307.log
    
    [mysqld3308]
    datadir = /opt/data/3308
    port = 3308
    socket = /tmp/mysql3308.sock
    pid-file = /opt/data/3308/mysql_3308.pid
    log-error=/var/log/3308.log

启动各实例

    [root@mr ~]# mysqld_multi start 3306
    [root@mr ~]# ss -antl
    State        Recv-Q       Send-Q             Local Address:Port             Peer Address:Port      Process      
    LISTEN       0            128                      0.0.0.0:22                    0.0.0.0:*                      
    LISTEN       0            80                             *:3306                        *:*                      
    LISTEN       0            128                         [::]:22                       [::]:*                      
    [root@mr ~]# mysqld_multi start 3307
    [root@mr ~]# mysqld_multi start 3308
    [root@mr ~]# ss -antl
    State        Recv-Q       Send-Q             Local Address:Port             Peer Address:Port      Process      
    LISTEN       0            128                      0.0.0.0:22                    0.0.0.0:*                      
    LISTEN       0            80                             *:3306                        *:*                      
    LISTEN       0            80                             *:3307                        *:*                      
    LISTEN       0            80                             *:3308                        *:*                      
    LISTEN       0            128                         [::]:22                       [::]:*                      
    [root@mr ~]# 

初始化密码

    [root@mr ~]# ls
    3306  3307  3308  anaconda-ks.cfg
    [root@mr ~]# cat 3306
    ds=WqMxEw5B%
    [root@mr ~]# mysql -uroot -pds=WqMxEw5B% -S /tmp/mysql3306.sock
    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 2
    Server version: 5.7.38
    
    Copyright (c) 2000, 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 '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> set password = password('3306');
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> exit
    Bye
    [root@mr ~]# mysql -uroot -p3306 -S /tmp/mysql3306.sock
    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 4
    Server version: 5.7.38 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 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 '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> 
    
    [root@mr ~]# cat 3307
    LzXC#WG-x1Kd
    [root@mr ~]# mysql -uroot -pLzXC#WG-x1Kd -S /tmp/mysql3307.sock
    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 2
    Server version: 5.7.38
    
    Copyright (c) 2000, 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 '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> set password = password('3307');
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> exit
    Bye
    [root@mr ~]# mysql -uroot -p3307 -S /tmp/mysql3307.sock
    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 4
    Server version: 5.7.38 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 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 '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> 
    
    
    [root@mr ~]# cat 3308
    ,L&rJy+Sm9z:
    [root@mr ~]# mysql -uroot -p',L&rJy+Sm9z:' -S /tmp/mysql3308.sock
    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 2
    Server version: 5.7.38
    
    Copyright (c) 2000, 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 '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> set password = password('3308');
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> exit
    Bye
    [root@mr ~]# mysql -uroot -p'3308' -S /tmp/mysql3308.sock
    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 4
    Server version: 5.7.38 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 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 '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> 
 
  • 分享:
评论
还没有评论
    发表评论 说点什么