[一、基本概念]()
[1]()、MySQL多实例
就是在一台机器上面开启多个不同的端口，运行多个MySQL服务进程。这些MySQL多实例公用一套安装程序，使用不同的(也可以是相同的)配置文件，启动程序，数据文件。在提供服务时候，多实例MySQL在逻辑上看来是各自独立的，多个实例的自身是根据配置文件对应的设定值，来取得相关硬件资源的多少。
[2]()、应用场景
1.采用了数据伪分布式架构的原因，而项目启动初期又不一定有那多的用户量，为此先一组物理数据库服务器，但部署多个实例，方便后续迁移
- 为规避mysql对SMP架构不支持的缺陷，使用多实例绑定处理器的办法，把不同的数据库分配到不同的实例上提供数据服务
- 一台物理数据库服务器支撑多个数据库的数据服务，为提高mysql复制的从机的恢复效率，采用多实例部署
- 已经为双主复制的mysql数据库服务器架构，想部分重要业务的数据多一份异地机房的热备份，而mysql复制暂不支持多主的复制模式，且不给用户提供服 务，为有效控制成本，会考虑异地机房部署一台性能超好的物理服务器，甚至外加磁盘柜的方式，为此也会部署多实例；
- 传统游戏行业的MMO/MMORPG，以及Web Game，每一个服都对应一个数据库，而可能要做很多数据查询和数据订正的工作，为减少维护而出错的概率，也可能采用多实例部署的方式，按区的概念分配数据库；
[3]()、作用
1、有效利用服务器的资源，单个服务器资源有剩余时，可以充分利用剩余的资源提供更多的服务。
2、资源互相争抢问题，比如 内存 ,cpu 需要开启 numa，并把 mysql 绑定到固定的核心上，网卡的中断请求，资源争用，最重要的是磁盘 IO。
[4]()、MySQL多实例配置方法
1 、单一配置文件
2 、多配置文件。
[二、MySQL]()多实例配置(3)
[安装]()
[root@localhost ~]# tar xf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz
[root@localhost ~]# mv mysql-5.6.40-linux-glibc2.12-x86_64 /usr/local/mysql
[root@localhost ~]# useradd -M -s mysql
[root@localhost ~]# ln -s /usr/local/mysql/bin/* /usr/local/bin/
[root@localhost ~]# chown -R mysql:mysql /data/mysql/ /usr/local/mysql/
[root@localhost ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld[配置多实例]()
[单配置文件]()
[root@localhost ~]# mkdir -p /data/mysql/mysql_330{6,7,8}
[root@localhost ~]# mkdir /data/mysql/mysql_330{6,7,8}/{data,log,tmp}
[root@localhost ~]# cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
cp：是否覆盖"/etc/my.cnf"？ y
[root@localhost ~]# vim /etc/my.cnf
[client]
port=3306
socket=/tmp/mysql.sock
[mysqld_multi]
mysqld = /usr/local/mysql /bin/mysqld_safe
mysqladmin = /usr/local/mysql /bin/mysqladmin
log = /data/mysql/mysqld_multi.log
[mysqld]
user=mysql
basedir = /usr/local/mysql
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld3306]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/data/mysql/mysql_3306/data
port=3306
server_id=3306
socket=/tmp/mysql_3306.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql_3306/log/slow.log
log-error = /data/mysql/mysql_3306/log/error.log
binlog_format = mixed
log-bin = /data/mysql/mysql_3306/log/mysql3306_bin
[mysqld3307]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/data/mysql/mysql_3307/data
port=3307
server_id=3307
socket=/tmp/mysql_3307.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql_3307/log/slow.log
log-error = /data/mysql/mysql_3307/log/error.log
binlog_format = mixed
log-bin = /data/mysql/mysql_3307/log/mysql3307_bin
[mysqld3308]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/data/mysql/mysql_3308/data
port=3308
server_id=3308
socket=/tmp/mysql_3308.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql_3308/log/slow.log
log-error = /data/mysql/mysql_3308/log/error.log
binlog_format = mixed
log-bin = /data/mysql/mysql_3308/log/mysql3308_bin[多配置文件]()
[root@localhost ~]# mkdir -p /data/3309/data
[root@localhost ~]# chown -R mysql:mysql /data/3309/
[root@localhost ~]# /usr/local/mysql/scripts/mysql_install_db --datadir=/data/3309/data/ --user=mysql --basedir=/usr/local/mysql/
Installing MySQL system tables...2018-05-30 21:39:08 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-05-30 21:39:08 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2018-05-30 21:39:08 0 [Note] /usr/local/mysql//bin/mysqld (mysqld 5.6.40) starting as process 63472 ...
2018-05-30 21:39:08 63472 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-05-30 21:39:08 63472 [Note] InnoDB: The InnoDB memory heap is disabled
2018-05-30 21:39:08 63472 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-05-30 21:39:08 63472 [Note] InnoDB: Memory barrier is not used
2018-05-30 21:39:08 63472 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-05-30 21:39:08 63472 [Note] InnoDB: Using Linux native AIO
2018-05-30 21:39:08 63472 [Note] InnoDB: Using CPU crc32 instructions
2018-05-30 21:39:08 63472 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-05-30 21:39:08 63472 [Note] InnoDB: Completed initialization of buffer pool
2018-05-30 21:39:08 63472 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2018-05-30 21:39:08 63472 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2018-05-30 21:39:08 63472 [Note] InnoDB: Database physically writes the file full: wait...
2018-05-30 21:39:08 63472 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2018-05-30 21:39:09 63472 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2018-05-30 21:39:09 63472 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2018-05-30 21:39:09 63472 [Warning] InnoDB: New log files created, LSN=45781
2018-05-30 21:39:09 63472 [Note] InnoDB: Doublewrite buffer not found: creating new
2018-05-30 21:39:09 63472 [Note] InnoDB: Doublewrite buffer created
2018-05-30 21:39:09 63472 [Note] InnoDB: 128 rollback segment(s) are active.
2018-05-30 21:39:09 63472 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-05-30 21:39:09 63472 [Note] InnoDB: Foreign key constraint system tables created
2018-05-30 21:39:09 63472 [Note] InnoDB: Creating tablespace and datafile system tables.
2018-05-30 21:39:09 63472 [Note] InnoDB: Tablespace and datafile system tables created.
2018-05-30 21:39:09 63472 [Note] InnoDB: Waiting for purge to start
2018-05-30 21:39:09 63472 [Note] InnoDB: 5.6.40 started; log sequence number 0
2018-05-30 21:39:09 63472 [Note] Binlog end
2018-05-30 21:39:09 63472 [Note] InnoDB: FTS optimize thread exiting.
2018-05-30 21:39:09 63472 [Note] InnoDB: Starting shutdown...
2018-05-30 21:39:11 63472 [Note] InnoDB: Shutdown completed; log sequence number 1625977
OK
Filling help tables...2018-05-30 21:39:11 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-05-30 21:39:11 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2018-05-30 21:39:11 0 [Note] /usr/local/mysql//bin/mysqld (mysqld 5.6.40) starting as process 63494 ...
2018-05-30 21:39:11 63494 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-05-30 21:39:11 63494 [Note] InnoDB: The InnoDB memory heap is disabled
2018-05-30 21:39:11 63494 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-05-30 21:39:11 63494 [Note] InnoDB: Memory barrier is not used
2018-05-30 21:39:11 63494 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-05-30 21:39:11 63494 [Note] InnoDB: Using Linux native AIO
2018-05-30 21:39:11 63494 [Note] InnoDB: Using CPU crc32 instructions
2018-05-30 21:39:11 63494 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-05-30 21:39:11 63494 [Note] InnoDB: Completed initialization of buffer pool
2018-05-30 21:39:11 63494 [Note] InnoDB: Highest supported file format is Barracuda.
2018-05-30 21:39:11 63494 [Note] InnoDB: 128 rollback segment(s) are active.
2018-05-30 21:39:11 63494 [Note] InnoDB: Waiting for purge to start
2018-05-30 21:39:11 63494 [Note] InnoDB: 5.6.40 started; log sequence number 1625977
2018-05-30 21:39:11 63494 [Note] Binlog end
2018-05-30 21:39:11 63494 [Note] InnoDB: FTS optimize thread exiting.
2018-05-30 21:39:11 63494 [Note] InnoDB: Starting shutdown...
2018-05-30 21:39:12 63494 [Note] InnoDB: Shutdown completed; log sequence number 1625987
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/local/mysql//bin/mysqladmin -u root password 'new-password'
/usr/local/mysql//bin/mysqladmin -u root -h localhost.localdomain password 'new-password'
Alternatively you can run:
/usr/local/mysql//bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd . ; /usr/local/mysql//bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl
Please report any problems at http://bugs.mysql.com/
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
WARNING: Found existing config file /usr/local/mysql//my.cnf on the system.
Because this file might be in use, it was not replaced,
but was used in bootstrap (unless you used --defaults-file)
and when you later start the server.
The new default config file was created as /usr/local/mysql//my-new.cnf,
please compare it with your file and take the changes you need.
WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server
[root@localhost ~]# cp /usr/local/mysql/support-files/my-default.cnf /data/3309/my.cnf
[root@localhost ~]# vim /data/3309/my.cnf
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[client]
port = 3309
socket = /tmp/mysql.sock1
[mysqld]
port = 3309
socket = /tmp/mysql.sock1
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
datadir=/data/3309/data
server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[root@localhost ~]# mysqld_safe --defaults-file=/data/3309/my.cnf 2&1> /dev/null
[root@localhost ~]# ss -tnl | grep 330
LISTEN 0 80 :::3309 :::*[初始化MySQL]()
[root@localhost ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3306/data --defaults-file=/etc/my.cnf
Installing MySQL system tables...2018-05-30 20:35:41 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-05-30 20:35:41 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2018-05-30 20:35:41 0 [Note] /usr/local/mysql//bin/mysqld (mysqld 5.6.40) starting as process 2451 ...
2018-05-30 20:35:41 2451 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-05-30 20:35:41 2451 [Note] InnoDB: The InnoDB memory heap is disabled
2018-05-30 20:35:41 2451 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-05-30 20:35:41 2451 [Note] InnoDB: Memory barrier is not used
2018-05-30 20:35:41 2451 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-05-30 20:35:41 2451 [Note] InnoDB: Using Linux native AIO
2018-05-30 20:35:41 2451 [Note] InnoDB: Using CPU crc32 instructions
2018-05-30 20:35:41 2451 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-05-30 20:35:41 2451 [Note] InnoDB: Completed initialization of buffer pool
2018-05-30 20:35:41 2451 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2018-05-30 20:35:41 2451 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2018-05-30 20:35:41 2451 [Note] InnoDB: Database physically writes the file full: wait...
2018-05-30 20:35:41 2451 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2018-05-30 20:35:41 2451 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2018-05-30 20:35:41 2451 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2018-05-30 20:35:41 2451 [Warning] InnoDB: New log files created, LSN=45781
2018-05-30 20:35:41 2451 [Note] InnoDB: Doublewrite buffer not found: creating new
2018-05-30 20:35:41 2451 [Note] InnoDB: Doublewrite buffer created
2018-05-30 20:35:41 2451 [Note] InnoDB: 128 rollback segment(s) are active.
2018-05-30 20:35:41 2451 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-05-30 20:35:41 2451 [Note] InnoDB: Foreign key constraint system tables created
2018-05-30 20:35:41 2451 [Note] InnoDB: Creating tablespace and datafile system tables.
2018-05-30 20:35:41 2451 [Note] InnoDB: Tablespace and datafile system tables created.
2018-05-30 20:35:41 2451 [Note] InnoDB: Waiting for purge to start
2018-05-30 20:35:41 2451 [Note] InnoDB: 5.6.40 started; log sequence number 0
2018-05-30 20:35:42 2451 [Note] Binlog end
2018-05-30 20:35:42 2451 [Note] InnoDB: FTS optimize thread exiting.
2018-05-30 20:35:42 2451 [Note] InnoDB: Starting shutdown...
2018-05-30 20:35:43 2451 [Note] InnoDB: Shutdown completed; log sequence number 1625977
OK #OK表示初始化成功
Filling help tables...2018-05-30 20:35:43 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-05-30 20:35:43 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2018-05-30 20:35:43 0 [Note] /usr/local/mysql//bin/mysqld (mysqld 5.6.40) starting as process 2473 ...
2018-05-30 20:35:43 2473 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-05-30 20:35:43 2473 [Note] InnoDB: The InnoDB memory heap is disabled
2018-05-30 20:35:43 2473 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-05-30 20:35:43 2473 [Note] InnoDB: Memory barrier is not used
2018-05-30 20:35:43 2473 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-05-30 20:35:43 2473 [Note] InnoDB: Using Linux native AIO
2018-05-30 20:35:43 2473 [Note] InnoDB: Using CPU crc32 instructions
2018-05-30 20:35:43 2473 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-05-30 20:35:43 2473 [Note] InnoDB: Completed initialization of buffer pool
2018-05-30 20:35:43 2473 [Note] InnoDB: Highest supported file format is Barracuda.
2018-05-30 20:35:43 2473 [Note] InnoDB: 128 rollback segment(s) are active.
2018-05-30 20:35:43 2473 [Note] InnoDB: Waiting for purge to start
2018-05-30 20:35:43 2473 [Note] InnoDB: 5.6.40 started; log sequence number 1625977
2018-05-30 20:35:43 2473 [Note] Binlog end
2018-05-30 20:35:43 2473 [Note] InnoDB: FTS optimize thread exiting.
2018-05-30 20:35:43 2473 [Note] InnoDB: Starting shutdown...
2018-05-30 20:35:45 2473 [Note] InnoDB: Shutdown completed; log sequence number 1625987
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/local/mysql//bin/mysqladmin -u root password 'new-password'
/usr/local/mysql//bin/mysqladmin -u root -h localhost.localdomain password 'new-password'
Alternatively you can run:
/usr/local/mysql//bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd . ; /usr/local/mysql//bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl
Please report any problems at http://bugs.mysql.com/
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
New default config file was created as /usr/local/mysql//my.cnf and
will be used by default by the server when you start it.
You may edit this file to change server settings
WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server
[root@localhost ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3307/data --defaults-file=/etc/my.cnf
Installing MySQL system tables...2018-05-30 20:35:50 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-05-30 20:35:50 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2018-05-30 20:35:50 0 [Note] /usr/local/mysql//bin/mysqld (mysqld 5.6.40) starting as process 2499 ...
2018-05-30 20:35:50 2499 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-05-30 20:35:50 2499 [Note] InnoDB: The InnoDB memory heap is disabled
2018-05-30 20:35:50 2499 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-05-30 20:35:50 2499 [Note] InnoDB: Memory barrier is not used
2018-05-30 20:35:50 2499 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-05-30 20:35:50 2499 [Note] InnoDB: Using Linux native AIO
2018-05-30 20:35:50 2499 [Note] InnoDB: Using CPU crc32 instructions
2018-05-30 20:35:50 2499 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-05-30 20:35:50 2499 [Note] InnoDB: Completed initialization of buffer pool
2018-05-30 20:35:50 2499 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2018-05-30 20:35:50 2499 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2018-05-30 20:35:50 2499 [Note] InnoDB: Database physically writes the file full: wait...
2018-05-30 20:35:50 2499 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2018-05-30 20:35:50 2499 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2018-05-30 20:35:50 2499 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2018-05-30 20:35:50 2499 [Warning] InnoDB: New log files created, LSN=45781
2018-05-30 20:35:50 2499 [Note] InnoDB: Doublewrite buffer not found: creating new
2018-05-30 20:35:50 2499 [Note] InnoDB: Doublewrite buffer created
2018-05-30 20:35:50 2499 [Note] InnoDB: 128 rollback segment(s) are active.
2018-05-30 20:35:50 2499 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-05-30 20:35:50 2499 [Note] InnoDB: Foreign key constraint system tables created
2018-05-30 20:35:50 2499 [Note] InnoDB: Creating tablespace and datafile system tables.
2018-05-30 20:35:50 2499 [Note] InnoDB: Tablespace and datafile system tables created.
2018-05-30 20:35:50 2499 [Note] InnoDB: Waiting for purge to start
2018-05-30 20:35:50 2499 [Note] InnoDB: 5.6.40 started; log sequence number 0
2018-05-30 20:35:50 2499 [Note] Binlog end
2018-05-30 20:35:50 2499 [Note] InnoDB: FTS optimize thread exiting.
2018-05-30 20:35:50 2499 [Note] InnoDB: Starting shutdown...
2018-05-30 20:35:52 2499 [Note] InnoDB: Shutdown completed; log sequence number 1625977
OK
Filling help tables...2018-05-30 20:35:52 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-05-30 20:35:52 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2018-05-30 20:35:52 0 [Note] /usr/local/mysql//bin/mysqld (mysqld 5.6.40) starting as process 2521 ...
2018-05-30 20:35:52 2521 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-05-30 20:35:52 2521 [Note] InnoDB: The InnoDB memory heap is disabled
2018-05-30 20:35:52 2521 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-05-30 20:35:52 2521 [Note] InnoDB: Memory barrier is not used
2018-05-30 20:35:52 2521 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-05-30 20:35:52 2521 [Note] InnoDB: Using Linux native AIO
2018-05-30 20:35:52 2521 [Note] InnoDB: Using CPU crc32 instructions
2018-05-30 20:35:52 2521 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-05-30 20:35:52 2521 [Note] InnoDB: Completed initialization of buffer pool
2018-05-30 20:35:52 2521 [Note] InnoDB: Highest supported file format is Barracuda.
2018-05-30 20:35:52 2521 [Note] InnoDB: 128 rollback segment(s) are active.
2018-05-30 20:35:52 2521 [Note] InnoDB: Waiting for purge to start
2018-05-30 20:35:52 2521 [Note] InnoDB: 5.6.40 started; log sequence number 1625977
2018-05-30 20:35:52 2521 [Note] Binlog end
2018-05-30 20:35:52 2521 [Note] InnoDB: FTS optimize thread exiting.
2018-05-30 20:35:52 2521 [Note] InnoDB: Starting shutdown...
2018-05-30 20:35:54 2521 [Note] InnoDB: Shutdown completed; log sequence number 1625987
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/local/mysql//bin/mysqladmin -u root password 'new-password'
/usr/local/mysql//bin/mysqladmin -u root -h localhost.localdomain password 'new-password'
Alternatively you can run:
/usr/local/mysql//bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd . ; /usr/local/mysql//bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl
Please report any problems at http://bugs.mysql.com/
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
WARNING: Found existing config file /usr/local/mysql//my.cnf on the system.
Because this file might be in use, it was not replaced,
but was used in bootstrap (unless you used --defaults-file)
and when you later start the server.
The new default config file was created as /usr/local/mysql//my-new.cnf,
please compare it with your file and take the changes you need.
WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server
[root@localhost ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3308/data --defaults-file=/etc/my.cnf
Installing MySQL system tables...2018-05-30 20:35:57 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-05-30 20:35:57 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2018-05-30 20:35:57 0 [Note] /usr/local/mysql//bin/mysqld (mysqld 5.6.40) starting as process 2547 ...
2018-05-30 20:35:57 2547 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-05-30 20:35:57 2547 [Note] InnoDB: The InnoDB memory heap is disabled
2018-05-30 20:35:57 2547 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-05-30 20:35:57 2547 [Note] InnoDB: Memory barrier is not used
2018-05-30 20:35:57 2547 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-05-30 20:35:57 2547 [Note] InnoDB: Using Linux native AIO
2018-05-30 20:35:57 2547 [Note] InnoDB: Using CPU crc32 instructions
2018-05-30 20:35:57 2547 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-05-30 20:35:57 2547 [Note] InnoDB: Completed initialization of buffer pool
2018-05-30 20:35:57 2547 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2018-05-30 20:35:57 2547 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2018-05-30 20:35:57 2547 [Note] InnoDB: Database physically writes the file full: wait...
2018-05-30 20:35:57 2547 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2018-05-30 20:35:57 2547 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2018-05-30 20:35:57 2547 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2018-05-30 20:35:57 2547 [Warning] InnoDB: New log files created, LSN=45781
2018-05-30 20:35:57 2547 [Note] InnoDB: Doublewrite buffer not found: creating new
2018-05-30 20:35:57 2547 [Note] InnoDB: Doublewrite buffer created
2018-05-30 20:35:57 2547 [Note] InnoDB: 128 rollback segment(s) are active.
2018-05-30 20:35:57 2547 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-05-30 20:35:57 2547 [Note] InnoDB: Foreign key constraint system tables created
2018-05-30 20:35:57 2547 [Note] InnoDB: Creating tablespace and datafile system tables.
2018-05-30 20:35:57 2547 [Note] InnoDB: Tablespace and datafile system tables created.
2018-05-30 20:35:57 2547 [Note] InnoDB: Waiting for purge to start
2018-05-30 20:35:57 2547 [Note] InnoDB: 5.6.40 started; log sequence number 0
2018-05-30 20:35:57 2547 [Note] Binlog end
2018-05-30 20:35:57 2547 [Note] InnoDB: FTS optimize thread exiting.
2018-05-30 20:35:57 2547 [Note] InnoDB: Starting shutdown...
2018-05-30 20:35:59 2547 [Note] InnoDB: Shutdown completed; log sequence number 1625977
OK
Filling help tables...2018-05-30 20:35:59 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-05-30 20:35:59 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2018-05-30 20:35:59 0 [Note] /usr/local/mysql//bin/mysqld (mysqld 5.6.40) starting as process 2569 ...
2018-05-30 20:35:59 2569 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-05-30 20:35:59 2569 [Note] InnoDB: The InnoDB memory heap is disabled
2018-05-30 20:35:59 2569 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-05-30 20:35:59 2569 [Note] InnoDB: Memory barrier is not used
2018-05-30 20:35:59 2569 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-05-30 20:35:59 2569 [Note] InnoDB: Using Linux native AIO
2018-05-30 20:35:59 2569 [Note] InnoDB: Using CPU crc32 instructions
2018-05-30 20:35:59 2569 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-05-30 20:35:59 2569 [Note] InnoDB: Completed initialization of buffer pool
2018-05-30 20:35:59 2569 [Note] InnoDB: Highest supported file format is Barracuda.
2018-05-30 20:35:59 2569 [Note] InnoDB: 128 rollback segment(s) are active.
2018-05-30 20:35:59 2569 [Note] InnoDB: Waiting for purge to start
2018-05-30 20:35:59 2569 [Note] InnoDB: 5.6.40 started; log sequence number 1625977
2018-05-30 20:35:59 2569 [Note] Binlog end
2018-05-30 20:35:59 2569 [Note] InnoDB: FTS optimize thread exiting.
2018-05-30 20:35:59 2569 [Note] InnoDB: Starting shutdown...
2018-05-30 20:36:01 2569 [Note] InnoDB: Shutdown completed; log sequence number 1625987
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/local/mysql//bin/mysqladmin -u root password 'new-password'
/usr/local/mysql//bin/mysqladmin -u root -h localhost.localdomain password 'new-password'
Alternatively you can run:
/usr/local/mysql//bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd . ; /usr/local/mysql//bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl
Please report any problems at http://bugs.mysql.com/
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
WARNING: Found existing config file /usr/local/mysql//my.cnf on the system.
Because this file might be in use, it was not replaced,
but was used in bootstrap (unless you used --defaults-file)
and when you later start the server.
The new default config file was created as /usr/local/mysql//my-new.cnf,
please compare it with your file and take the changes you need.
WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server[mysqld_multi]()多实例管理
[root@localhost ~]# /usr/local/mysql/bin/mysqld_multi start #启动全部实例
[root@localhost ~]# /usr/local/mysql/bin/mysqld_multi report #查看全部实例状态
[root@localhost ~]# /usr/local/mysql/bin/mysqld_multi start 3306 #开启单个实例
[root@localhost ~]# /usr/local/mysql/bin/mysqld_multi stop 3306 #停止单个实例
[root@localhost ~]# /usr/local/mysql/bin/mysqld_multi report 3306 #查看单个实例状态
[root@localhost ~]# mysqladmin -S /tmp/mysql_3306.sock shutdown #单独关闭MySQL实例
[root@localhost ~]# killall mysqld #关闭MySQL，不建议使用
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running[MySQL]()简单优化
[root@localhost ~]# mysql -S /tmp/mysql_3306.sock #指定socket登陆
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.40-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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> show databases; #查询多余的表
+--------------------------------+
| Database |
+--------------------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------------------+
4 rows in set (0.01 sec)
mysql> drop database test; #删除多余的表
Query OK, 0 rows affected (0.09 sec)
mysql> select User,Host from mysql.user; #查询用户项及主机授权
+------+-------------------------------------+
| User | Host |
+------+-------------------------------------+
| root | 127.0.0.1 |
| root | ::1 |
| | localhost |
| root | localhost |
| | localhost.localdomain |
| root | localhost.localdomain |
+------+------------------------------------+
6 rows in set (0.00 sec)
mysql> delete from mysql.user where User=''; #去除多余为空
Query OK, 2 rows affected (0.00 sec)
mysql> delete from mysql.user where Host='::1';
Query OK, 1 row affected (0.00 sec)
mysql> delete from mysql.user where Host='localhost.localdomain';
Query OK, 1 row affected (0.00 sec)[简单管理脚本]()
[root@localhost ~]# vim /etc/init.d/multid
#!/bin/bash
#chkconfig: 35 12 25
basedir=/usr/local/mysql
bindir=/usr/local/mysql/bin
conf=/etc/my.cnf
export PATH=$bindir:/$PATH
if test -x $bindir/mysqld_multi
then
mysqld_multi="$bindir/mysqld_multi";
else
echo "Can't execute $bindir/mysqld_multi from dir $basedir";
exit;
fi
case "$1" in
'start' )
"$mysqld_multi" --defaults-extra-file=$conf start $2
;;
'stop' )
"$mysqld_multi" --defaults-extra-file=$conf stop $2
;;
'report' )
"$mysqld_multi" --defaults-extra-file=$conf report $2
;;
'restart' )
"$mysqld_multi" --defaults-extra-file=$conf stop $2
"$mysqld_multi" --defaults-extra-file=$conf start $2
;;
*)
echo "Usage: $0 {start|stop|report|restart}" >&2
;;
esac
[root@localhost ~]# chmod +x /etc/init.d/multid
[root@localhost ~]# service multid report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
[root@localhost ~]# service multid report 3308
Reporting MySQL servers
MySQL server from group: mysqld3308 is running
[root@localhost ~]# service multid stop
[root@localhost ~]# service multid report
Reporting MySQL servers
MySQL server from group: mysqld3306 is not running
MySQL server from group: mysqld3307 is not running
MySQL server from group: mysqld3308 is not running[三、常见报错信息]()
[1]()、源码安装
1 ：安装mysql 报错
checking for tgetent in -lncurses… no
checking for tgetent in -lcurses… no
checking for tgetent in -ltermcap… no
checking for tgetent in -ltinfo… no
checking for termcap functions library… configure: error: No curses/termcap library found原因：
缺少ncurses安装包
解决方法：yum -y install ncurses-devel
2：…/depcomp: line 571: exec: g++: not found
make[1]: [my_new.o] 错误 127
make[1]: Leaving directory `/home/justme/software/mysql-5.1.30/mysys’
make: [all-recursive] 错误 1解决方法:yum install gcc-c++
3：…/include/my_global.h:909: error: redeclaration of C++ built-in type bool' make[2]: *** [my_new.o] Error 1 make[2]: Leaving directory/home/tools/mysql-5.0.22/mysys’
make[1]: [all-recursive] Error 1
make[1]: Leaving directory `/home/tools/mysql-5.0.22’
make: [all] Error 2是因为gcc-c++是在configure之后安装的，此时只需重新configure后再编译make即可。
2：初始化数据库报错
报错现象：
[root@mysql mysql-6.0.11-alpha]# scripts/mysql_install_db --basedir=/usr/local/mysql/ --user=mysql
Installing MySQL system tables…
ERROR: 1136 Column count doesn’t match value count at row 1
150414 7:15:56 [ERROR] Aborting
150414 7:15:56 [Warning] Forcing shutdown of 1 plugins
150414 7:15:56 [Note] /usr/local/mysql//libexec/mysqld: Shutdown complete
Installation of system tables failed! Examine the logs in
/var/lib/mysql for more information.
You can try to start the mysqld daemon with:
shell> /usr/local/mysql//libexec/mysqld --skip-grant &
and use the command line tool /usr/local/mysql//bin/mysql
to connect to the mysql database and look at the grant tables:
shell> /usr/local/mysql//bin/mysql -u root mysql
mysql> show tables
Try ‘mysqld --help’ if you have problems with paths. Using --log
gives you a log in /var/lib/mysql that may be helpful.
The latest information about MySQL is available on the web at
http://www.mysql.com/. Please consult the MySQL manual section
‘Problems running mysql_install_db’, and the manual section that
describes problems on your OS. Another information source are the
MySQL email archives available at http://lists.mysql.com/.
Please check all of the above before mailing us! And remember, if
you do mail us, you MUST use the /usr/local/mysql//scripts/mysqlbug script!原因： 原有安装的mysql信息没有删除干净
解决方法： 删除/var/lib/mysql目录
其他待补充…
此处评论已关闭