mysql多实例部署
MySQL多实例
一、什么是MySQL多实例
MySQL的多实例就是在一台机器上开启多个不同的服务端口，运行多个MySQL服务进程，使用不同的socket来监听这多个不同的端口以此提供服务，这一点和Oracle的多实例类似。这些MySQL的实例共用相同的MySQL但是使用的参数文件是不一样的，相应的数据文件也不同。提供服务的时候从逻辑上看各自独立，各自获取的硬件资源可以灵活设定。
二、MySQL多实例优劣势
- 有效的利用服务器资源。当单个服务器的资源有剩余的时候可以将多余的资源有效的利用起来，而且还实现了资源的逻辑隔离。
- 节约经济消耗。例如需要多个数据库来搭建主从，但是又只有一台服务器。
- 当单个数据库并发很高或计算资源需求很高时。整个实例会消耗大量系统的CPU，IO等资源。这样其他实例的可利用资源就会变少产生问题。无法实现实例资源的绝对隔离。
三、如何部署MySQL多实例
部署的方式有两种：
1.使用mysqld_multi工具，用单独的配置文件实现多实例配置复杂但是管理方便。
2.设置多个配置文件启动，这样启动不同进程实现多实例。原理简单，但是不易管理。
mysql软件包网址：https://downloads.mysql.com/archives/community/
mysql多实例部署实例
1、下载mysql压缩包拉进/usr/src下面
[root@localhost ~]# cd /usr/src/
[root@localhost src]# ls
debug kernels mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
2、解压软件至/usr/local
[root@localhost src]# tar xf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost src]# cd ..
[root@localhost usr]# cd local/
[root@localhost local]# ls
bin games lib libexec sbin src
etc include lib64 mysql-5.7.34-linux-glibc2.12-x86_64 share
3、做一个软连接
[root@localhost local]# ln -s /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/ /usr/local/mysql
[root@localhost local]# ll
总用量 0
drwxr-xr-x. 2 root root 6 8月 12 2018 bin
drwxr-xr-x. 2 root root 6 8月 12 2018 etc
drwxr-xr-x. 2 root root 6 8月 12 2018 games
drwxr-xr-x. 2 root root 6 8月 12 2018 include
drwxr-xr-x. 2 root root 6 8月 12 2018 lib
drwxr-xr-x. 2 root root 6 8月 12 2018 lib64
drwxr-xr-x. 2 root root 6 8月 12 2018 libexec
lrwxrwxrwx. 1 root root 47 8月 29 20:23 mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 8月 29 20:19 mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 8月 12 2018 sbin
drwxr-xr-x. 5 root root 49 8月 24 23:44 share
drwxr-xr-x. 2 root root 6 8月 12 2018 src
4、创建用户和组及修改属主属组
[root@localhost src]# useradd -r -M -s /sbin/nologin mysql
[root@localhost src]# grep mysql /etc/group
mysql:x:975:
[root@localhost local]# chown -R mysql.mysql mysql*
[root@localhost local]# ll
总用量 0
drwxr-xr-x. 2 root root 6 8月 12 2018 bin
drwxr-xr-x. 2 root root 6 8月 12 2018 etc
drwxr-xr-x. 2 root root 6 8月 12 2018 games
drwxr-xr-x. 2 root root 6 8月 12 2018 include
drwxr-xr-x. 2 root root 6 8月 12 2018 lib
drwxr-xr-x. 2 root root 6 8月 12 2018 lib64
drwxr-xr-x. 2 root root 6 8月 12 2018 libexec
lrwxrwxrwx. 1 mysql mysql 47 8月 29 20:30 mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 mysql mysql 129 8月 29 20:29 mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 8月 12 2018 sbin
drwxr-xr-x. 5 root root 49 8月 24 23:44 share
drwxr-xr-x. 2 root root 6 8月 12 2018 src
5、添加环境变量
[root@localhost ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost ~]# . /etc/profile.d/mysql.sh
[root@localhost ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@localhost ~]#
创建各实例数据事件的目录
[root@localhost ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@localhost ~]# chown -R mysql.mysql /opt/data/
[root@localhost ~]# ll /opt/data/
总用量 0
drwxr-xr-x. 2 mysql mysql 6 8月 29 20:39 3306
drwxr-xr-x. 2 mysql mysql 6 8月 29 20:39 3307
drwxr-xr-x. 2 mysql mysql 6 8月 29 20:39 3308
[root@localhost ~]# tree /opt/data/
/opt/data/
├── 3306
├── 3307
└── 3308
3 directories, 0 files
初始化各实例
[root@localhost ~]# mysqld --lnitialize --user mysql --datadir /opt/data/3306
2021-08-29T12:54:52.708476Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T12:54:52.708527Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2021-08-29T12:54:52.708542Z 0 [Note] mysqld (mysqld 5.7.34) starting as process 28779 ...
2021-08-29T12:54:52.723568Z 0 [Note] InnoDB: PUNCH HOLE support available
2021-08-29T12:54:52.723603Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-08-29T12:54:52.723605Z 0 [Note] InnoDB: Uses event mutexes
2021-08-29T12:54:52.723607Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2021-08-29T12:54:52.723609Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-08-29T12:54:52.723611Z 0 [Note] InnoDB: Using Linux native AIO
2021-08-29T12:54:52.723819Z 0 [Note] InnoDB: Number of pools: 1
2021-08-29T12:54:52.723929Z 0 [Note] InnoDB: Using CPU crc32 instructions
2021-08-29T12:54:52.727696Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2021-08-29T12:54:52.740779Z 0 [Note] InnoDB: Completed initialization of buffer pool
2021-08-29T12:54:52.744859Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-08-29T12:54:52.761937Z 0 [Note] InnoDB: The first innodb_system data file 'ibdata1' did not exist. A new tablespace will be created!
2021-08-29T12:54:52.762292Z 0 [Note] InnoDB: Setting file './ibdata1' size to 12 MB. Physically writing the file full; Please wait ...
2021-08-29T12:54:52.788277Z 0 [Note] InnoDB: File './ibdata1' size is now 12 MB.
2021-08-29T12:54:52.788590Z 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2021-08-29T12:54:52.873092Z 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2021-08-29T12:54:52.950079Z 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2021-08-29T12:54:52.950168Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T12:54:52.950180Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-08-29T12:54:52.950338Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-08-29T12:54:52.961626Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2021-08-29T12:54:52.961744Z 0 [Note] InnoDB: Doublewrite buffer not found: creating new
2021-08-29T12:54:52.966460Z 0 [Note] InnoDB: Doublewrite buffer created
2021-08-29T12:54:52.970928Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2021-08-29T12:54:52.970937Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2021-08-29T12:54:52.971295Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T12:54:52.976621Z 0 [Note] InnoDB: Foreign key constraint system tables created
2021-08-29T12:54:52.976654Z 0 [Note] InnoDB: Creating tablespace and datafile system tables.
2021-08-29T12:54:52.977082Z 0 [Note] InnoDB: Tablespace and datafile system tables created.
2021-08-29T12:54:52.977098Z 0 [Note] InnoDB: Creating sys_virtual system tables.
2021-08-29T12:54:52.977295Z 0 [Note] InnoDB: sys_virtual table created
2021-08-29T12:54:52.977490Z 0 [Note] InnoDB: Waiting for purge to start
2021-08-29T12:54:53.028210Z 0 [Note] InnoDB: 5.7.34 started; log sequence number 0
2021-08-29T12:54:53.029092Z 0 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Table 'mysql.plugin' doesn't exist
2021-08-29T12:54:53.029185Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2021-08-29T12:54:53.029243Z 0 [ERROR] unknown option '--lnitialize'
2021-08-29T12:54:53.029247Z 0 [ERROR] Aborting
2021-08-29T12:54:53.029250Z 0 [Note] Binlog end
2021-08-29T12:54:53.029274Z 0 [Note] Shutting down plugin 'ngram'
2021-08-29T12:54:53.029277Z 0 [Note] Shutting down plugin 'partition'
2021-08-29T12:54:53.029279Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2021-08-29T12:54:53.029281Z 0 [Note] Shutting down plugin 'ARCHIVE'
2021-08-29T12:54:53.029282Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2021-08-29T12:54:53.029304Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2021-08-29T12:54:53.029305Z 0 [Note] Shutting down plugin 'MyISAM'
2021-08-29T12:54:53.029311Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2021-08-29T12:54:53.029312Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2021-08-29T12:54:53.029314Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2021-08-29T12:54:53.029315Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2021-08-29T12:54:53.029316Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2021-08-29T12:54:53.029317Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2021-08-29T12:54:53.029318Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2021-08-29T12:54:53.029319Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2021-08-29T12:54:53.029320Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2021-08-29T12:54:53.029321Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2021-08-29T12:54:53.029323Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2021-08-29T12:54:53.029324Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2021-08-29T12:54:53.029325Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2021-08-29T12:54:53.029326Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2021-08-29T12:54:53.029327Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2021-08-29T12:54:53.029328Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2021-08-29T12:54:53.029329Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2021-08-29T12:54:53.029330Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2021-08-29T12:54:53.029331Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2021-08-29T12:54:53.029332Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2021-08-29T12:54:53.029334Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2021-08-29T12:54:53.029335Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2021-08-29T12:54:53.029336Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2021-08-29T12:54:53.029337Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2021-08-29T12:54:53.029338Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2021-08-29T12:54:53.029339Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2021-08-29T12:54:53.029340Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2021-08-29T12:54:53.029341Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2021-08-29T12:54:53.029342Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2021-08-29T12:54:53.029343Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2021-08-29T12:54:53.029344Z 0 [Note] Shutting down plugin 'InnoDB'
2021-08-29T12:54:53.029664Z 0 [Note] InnoDB: FTS optimize thread exiting.
2021-08-29T12:54:53.030052Z 0 [Note] InnoDB: Starting shutdown...
2021-08-29T12:54:53.130850Z 0 [Note] InnoDB: Dumping buffer pool(s) to /opt/data/3306/ib_buffer_pool
2021-08-29T12:54:53.130967Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 210829 20:54:53
2021-08-29T12:54:54.643890Z 0 [Note] InnoDB: Shutdown completed; log sequence number 1209961
2021-08-29T12:54:54.644295Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2021-08-29T12:54:54.644304Z 0 [Note] Shutting down plugin 'MEMORY'
2021-08-29T12:54:54.644307Z 0 [Note] Shutting down plugin 'CSV'
2021-08-29T12:54:54.644310Z 0 [Note] Shutting down plugin 'sha256_password'
2021-08-29T12:54:54.644311Z 0 [Note] Shutting down plugin 'mysql_native_password'
2021-08-29T12:54:54.644455Z 0 [Note] Shutting down plugin 'binlog'
2021-08-29T12:54:54.644540Z 0 [Note] mysqld: Shutdown complete
[root@localhost ~]# echo 'jf:*4eh;*jq5' > 6pass
[root@localhost ~]# mysqld --lnitialize --user mysql --datadir /opt/data/3307
mysqld: Can't change dir to '/opt/data/3307/' (Errcode: 2 - No such file or directory)
2021-08-29T12:55:02.120394Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T12:55:02.120451Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2021-08-29T12:55:02.120464Z 0 [Note] mysqld (mysqld 5.7.34) starting as process 29102 ...
2021-08-29T12:55:02.121687Z 0 [Warning] Can't create test file /opt/data/3307/localhost.lower-test
2021-08-29T12:55:02.121696Z 0 [Warning] Can't create test file /opt/data/3307/localhost.lower-test
2021-08-29T12:55:02.123527Z 0 [ERROR] failed to set datadir to /opt/data/3307/
2021-08-29T12:55:02.123534Z 0 [ERROR] Aborting
2021-08-29T12:55:02.123537Z 0 [Note] Binlog end
2021-08-29T12:55:02.123552Z 0 [Note] mysqld: Shutdown complete
[root@localhost ~]# echo 'jf:*4eh;*jq5' > 7pass
[root@localhost ~]# mysqld --lnitialize --user mysql --datadir /opt/data/3308
2021-08-29T12:55:18.711442Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T12:55:18.711493Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2021-08-29T12:55:18.711505Z 0 [Note] mysqld (mysqld 5.7.34) starting as process 29565 ...
2021-08-29T12:55:18.718185Z 0 [Note] InnoDB: PUNCH HOLE support available
2021-08-29T12:55:18.718197Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-08-29T12:55:18.718199Z 0 [Note] InnoDB: Uses event mutexes
2021-08-29T12:55:18.718201Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2021-08-29T12:55:18.718203Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-08-29T12:55:18.718204Z 0 [Note] InnoDB: Using Linux native AIO
2021-08-29T12:55:18.718324Z 0 [Note] InnoDB: Number of pools: 1
2021-08-29T12:55:18.718373Z 0 [Note] InnoDB: Using CPU crc32 instructions
2021-08-29T12:55:18.720380Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2021-08-29T12:55:18.724890Z 0 [Note] InnoDB: Completed initialization of buffer pool
2021-08-29T12:55:18.727710Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-08-29T12:55:18.736462Z 0 [Note] InnoDB: The first innodb_system data file 'ibdata1' did not exist. A new tablespace will be created!
2021-08-29T12:55:18.736654Z 0 [Note] InnoDB: Setting file './ibdata1' size to 12 MB. Physically writing the file full; Please wait ...
2021-08-29T12:55:18.749680Z 0 [Note] InnoDB: File './ibdata1' size is now 12 MB.
2021-08-29T12:55:18.749898Z 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2021-08-29T12:55:18.814710Z 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2021-08-29T12:55:18.881061Z 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2021-08-29T12:55:18.881097Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T12:55:18.881104Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-08-29T12:55:18.881141Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-08-29T12:55:18.896581Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2021-08-29T12:55:18.896716Z 0 [Note] InnoDB: Doublewrite buffer not found: creating new
2021-08-29T12:55:18.901884Z 0 [Note] InnoDB: Doublewrite buffer created
2021-08-29T12:55:18.907194Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2021-08-29T12:55:18.907212Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2021-08-29T12:55:18.907363Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T12:55:18.908983Z 0 [Note] InnoDB: Foreign key constraint system tables created
2021-08-29T12:55:18.909001Z 0 [Note] InnoDB: Creating tablespace and datafile system tables.
2021-08-29T12:55:18.909437Z 0 [Note] InnoDB: Tablespace and datafile system tables created.
2021-08-29T12:55:18.909453Z 0 [Note] InnoDB: Creating sys_virtual system tables.
2021-08-29T12:55:18.909710Z 0 [Note] InnoDB: sys_virtual table created
2021-08-29T12:55:18.910051Z 0 [Note] InnoDB: Waiting for purge to start
2021-08-29T12:55:18.960607Z 0 [Note] InnoDB: 5.7.34 started; log sequence number 0
2021-08-29T12:55:18.961062Z 0 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Table 'mysql.plugin' doesn't exist
2021-08-29T12:55:18.961138Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2021-08-29T12:55:18.961266Z 0 [ERROR] unknown option '--lnitialize'
2021-08-29T12:55:18.961273Z 0 [ERROR] Aborting
2021-08-29T12:55:18.961277Z 0 [Note] Binlog end
2021-08-29T12:55:18.961317Z 0 [Note] Shutting down plugin 'ngram'
2021-08-29T12:55:18.961320Z 0 [Note] Shutting down plugin 'partition'
2021-08-29T12:55:18.961321Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2021-08-29T12:55:18.961323Z 0 [Note] Shutting down plugin 'ARCHIVE'
2021-08-29T12:55:18.961324Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2021-08-29T12:55:18.961345Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2021-08-29T12:55:18.961367Z 0 [Note] Shutting down plugin 'MyISAM'
2021-08-29T12:55:18.961374Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2021-08-29T12:55:18.961376Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2021-08-29T12:55:18.961377Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2021-08-29T12:55:18.961378Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2021-08-29T12:55:18.961380Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2021-08-29T12:55:18.961396Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2021-08-29T12:55:18.961397Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2021-08-29T12:55:18.961398Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2021-08-29T12:55:18.961399Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2021-08-29T12:55:18.961400Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2021-08-29T12:55:18.961401Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2021-08-29T12:55:18.961402Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2021-08-29T12:55:18.961403Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2021-08-29T12:55:18.961425Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2021-08-29T12:55:18.961426Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2021-08-29T12:55:18.961428Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2021-08-29T12:55:18.961429Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2021-08-29T12:55:18.961430Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2021-08-29T12:55:18.961431Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2021-08-29T12:55:18.961433Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2021-08-29T12:55:18.961450Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2021-08-29T12:55:18.961451Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2021-08-29T12:55:18.961453Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2021-08-29T12:55:18.961454Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2021-08-29T12:55:18.961455Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2021-08-29T12:55:18.961456Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2021-08-29T12:55:18.961457Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2021-08-29T12:55:18.961479Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2021-08-29T12:55:18.961480Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2021-08-29T12:55:18.961482Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2021-08-29T12:55:18.961483Z 0 [Note] Shutting down plugin 'InnoDB'
2021-08-29T12:55:18.961828Z 0 [Note] InnoDB: FTS optimize thread exiting.
2021-08-29T12:55:18.962093Z 0 [Note] InnoDB: Starting shutdown...
2021-08-29T12:55:19.063555Z 0 [Note] InnoDB: Dumping buffer pool(s) to /opt/data/3308/ib_buffer_pool
2021-08-29T12:55:19.063674Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 210829 20:55:19
2021-08-29T12:55:20.577541Z 0 [Note] InnoDB: Shutdown completed; log sequence number 1209961
2021-08-29T12:55:20.577997Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2021-08-29T12:55:20.578007Z 0 [Note] Shutting down plugin 'MEMORY'
2021-08-29T12:55:20.578010Z 0 [Note] Shutting down plugin 'CSV'
2021-08-29T12:55:20.578014Z 0 [Note] Shutting down plugin 'sha256_password'
2021-08-29T12:55:20.578015Z 0 [Note] Shutting down plugin 'mysql_native_password'
2021-08-29T12:55:20.578101Z 0 [Note] Shutting down plugin 'binlog'
2021-08-29T12:55:20.578264Z 0 [Note] mysqld: Shutdown complete
[root@localhost ~]# echo 'jf:*4eh;*jq5' > 8pass
[root@localhost ~]# ls
6pass 8pass 模板 图片 下载 桌面 initial-setup-ks.cfg
7pass 公共 视频 文档 音乐 anaconda-ks.cfg
[root@localhost ~]#
安装prel
[root@localhost ~]# yum -y install perl
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
AppStream 3.1 MB/s | 3.2 kB 00:00
BaseOS 2.7 MB/s | 2.7 kB 00:00
依赖关系解决。
=====================================================================================
软件包 架构 版本 仓库 大小
=====================================================================================
Installing:
安装mysql命令
[root@localhost ~]# ldd /usr/local/mysql/bin/mysql
linux-vdso.so.1 (0x00007ffdfef3a000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fa19ea4a000)
librt.so.1 => /lib64/librt.so.1 (0x00007fa19e841000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007fa19e63d000)
libncurses.so.5 => not found
libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007fa19e2a8000)
libm.so.6 => /lib64/libm.so.6 (0x00007fa19df26000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fa19dd0e000)
libc.so.6 => /lib64/libc.so.6 (0x00007fa19d94a000)
/lib64/ld-linux-x86-64.so.2 (0x00007fa19ec6a000)
libtinfo.so.5 => not found
[root@localhost ~]# yum whatprovides libncurses.so.5
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
上次元数据过期检查：0:02:11 前，执行于 2021年08月29日 星期日 20时56分40秒。
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility libraries
仓库 ：BaseOS
匹配来源：
提供 : libncurses.so.5
[root@localhost ~]# yum whatprovides libtinfo.so.5
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
上次元数据过期检查：0:02:33 前，执行于 2021年08月29日 星期日 20时56分40秒。
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility libraries
仓库 ：BaseOS
匹配来源：
提供 : libtinfo.so.5
[root@localhost ~]# yum -y install ncurses-compat-libs
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
上次元数据过期检查：0:02:48 前，执行于 2021年08月29日 星期日 20时56分40秒。
依赖关系解决。
====================================================================================
软件包 架构 版本 仓库 大小
====================================================================================
Installing:
ncurses-compat-libs x86_64 6.1-7.20180224.el8 BaseOS 331 k
事务概要
====================================================================================
安装 1 软件包
总计：331 k
安装大小：1.2 M
下载软件包：
运行事务检查
事务检查成功。
运行事务测试
事务测试成功。
运行事务
准备中 : 1/1
Installing : ncurses-compat-libs-6.1-7.20180224.el8.x86_64 1/1
运行脚本 : ncurses-compat-libs-6.1-7.20180224.el8.x86_64 1/1
验证 : ncurses-compat-libs-6.1-7.20180224.el8.x86_64 1/1
Installed products updated.
已安装:
ncurses-compat-libs-6.1-7.20180224.el8.x86_64
完毕！
配置配置文件/etc/my.cnf
[root@localhost ~]# cat /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@localhost ~]# mysqld_multi start
Wide character in print at /usr/local/mysql/bin/mysqld_multi line 678.
Installing new database in /opt/data/3306
2021-08-29T13:03:38.165504Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T13:03:38.166352Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2021-08-29T13:03:38.166367Z 0 [ERROR] Aborting
FATAL ERROR: Tried to start mysqld under group [mysqld3306],
but no data directory was found or could be created.
data directory used: /opt/data/3306
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 32 192.168.122.1:53 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
LISTEN 0 128 [::]:111 [::]:*
[root@localhost ~]# ps -ef|grep mysqld
root 73694 10482 0 21:04 pts/1 00:00:00 grep --color=auto mysqld
[root@localhost ~]#
初始化密码
[root@localhost ~]# mysql -uroot -p'Jf:*4eh;*jq5' -h127.0.0.1 -P3306
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.34
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.
mysql> set password = password('wjj');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# cat 7pass
soh48HPZmC&f
[root@localhost ~]# mysql -uroot -p'soh48HPZmC&f' -h127.0.0.1 -P3307
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.34
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.
mysql> set password = password('wjj');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# cat 8pass
w)Lo>+qki3OV
[root@localhost ~]# mysql -uroot -p'w)Lo>+qki3OV' -h127.0.0.1 -P3308
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.34
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.
mysql> set password = password('wjj');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit
Bye
设置开机自启
一、多实例设置开机自启
- 把/usr/local/mysql/bin/support_files/mysqld_multi.service文件复制到/etc/init.d/mysqld_multi
- 在/etc/init.d/mysqld_multi文件中增加环境变量设置exoprt PATH=/usr/local/mysql/bin:$PATH
- chkconfig --add mysqld_multi
Xtrabackup备份与恢复
一、Xtrabackup介绍
1.MySQL冷备、mysqldump、MySQL热拷贝都无法实现对数据库进行增量备份。在实际生产环境中增量备份是非常实用的，如果数据大于50G或100G，存储空间足够的情况下，可以每天进行完整备份，如果每天产生的数据量较大，需要定制数据备份策略。例如每周实用完整备份，周一到周六实用增量备份。而Percona-Xtrabackup就是为了实现增量备份而出现的一款主流备份工具，xtrabakackup有2个工具，分别是xtrabakup、innobakupe。
2.Percona-xtrabackup是 Percona公司开发的一个用于MySQL数据库物理热备的备份工具，支持MySQL、Percona server和MariaDB，开源免费，是目前较为受欢迎的主流备份工具。xtrabackup只能备份innoDB和xtraDB两种数据引擎的表，而不能备份MyISAM数据表。
二、Xtrabackup优点
- 备份速度快，物理备份可靠
- 备份过程不会打断正在执行的事务（无需锁表）
- 能够基于压缩等功能节约磁盘空间和流量
- 自动备份校验
- 还原速度快
- 可以流传将备份传输到另外一台机器上
- 在不增加服务器负载的情况备份数据
三、Xtrabackup备份原理
Xtrabackup备份流程图：
1.innobackupex启动后，会先fork一个进程，用于启动xtrabackup，然后等待xtrabackup备份ibd数据文件；
2.xtrabackup在备份innoDB数据是，有2种线程：redo拷贝线程和ibd数据拷贝线程。xtrabackup进程开始执行后，会启动一个redo拷贝的线程，用于从最新的checkpoint点开始顺序拷贝redo.log；再启动ibd数据拷贝线程，进行拷贝ibd数据。这里是先启动redo拷贝线程的。在此阶段，innobackupex进行处于等待状态（等待文件被创建）
3.xtrabackup拷贝完成ibd数据文件后，会通知innobackupex（通过创建文件），同时xtrabackup进入等待状态（redo线程依旧在拷贝redo.log）
4.innobackupex收到xtrabackup通知后哦，执行FLUSH TABLES WITH READ LOCK（FTWRL），取得一致性位点，然后开始备份非InnoDB文件（如frm、MYD、MYI、CSV、opt、par等格式的文件），在拷贝非InnoDB文件的过程当中，数据库处于全局只读状态。
5.当innobackup拷贝完所有的非InnoDB文件后，会通知xtrabackup，通知完成后，进入等待状态；
6.xtrabackup收到innobackupex备份完成的通知后，会停止redo拷贝线程，然后通知innobackupex，redo.log文件拷贝完成；
7.innobackupex收到redo.log备份完成后，就进行解锁操作，执行：UNLOCK TABLES；
8.最后innbackupex和xtrabackup进程各自释放资源，写备份元数据信息等，innobackupex等xtrabackup子进程结束后退出。
全量备份
[root@localhost ~]# mkdir backups
[root@localhost ~]# cd backups/
[root@localhost backups]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=zs --host=127.0.0.1 -P3306 /backups/
恢复
[root@localhost ~]# ls /backups/
2021-08-28_18-50-04
[root@localhost ~]# innobackupex --apply-log /backups/2021-08-28_18-50-04
[root@localhost ~]# cd /opt/data/3306
[root@localhost 3306]# ls
auto.cnf ibdata1 performance_schema
ca-key.pem ib_logfile0 private_key.pem
ca.pem ib_logfile1 public_key.pem
client-cert.pem ibtmp1 server-cert.pem
client-key.pem mysql server-key.pem
ib_buffer_pool mysql.pid sys
[root@localhost 3306]# rm -rf *
[root@localhost 3306]# ls
[root@localhost 3306]# innobackupex --defaults-file=/etc/my.cnf --copy-back -uroot -pzs --host=127.0.0.1 /backup/2021-08-28_18-50-04/
常用选项
常用选项:
--host 指定主机
--user 指定用户名
--password 指定密码
--port 指定端口
--databases 指定数据库
--incremental 创建增量备份
--incremental-basedir 指定包含完全备份的目录
--incremental-dir 指定包含增量备份的目录
--apply-log 对备份进行预处理操作
一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
--redo-only 不回滚未提交事务
--copy-back 恢复备份目录
浙公网安备 33010602011771号