本文主要演示如何在linux系统上通过mysql_install_db脚本初始化mysql数据库(实例)。本案例使用之前通过rpm包安装的mysql数据库环境，可能很多DBA在通过rpm安装完mysql数据库后，并不会使用自带的mysql数据库(实例)，因为rpm包安装的数据，软件和数据库的位置是不可以自己设置的；还有可能一台服务器上需要同时运行多个mysql数据库(实例)，就需要使用mysql_install_db脚本来初始化新的mysql数据库(实例)。下面是操作过程。

创建相应的目录，用来存放mysql的数据文件等。

[root@mysql-1 mysql]# mkdir -p /mysql/data/3307/{data,binlog,tmp}

创建my.cnf文件，内容如下：

[root@mysql-1 3307]# cat /mysql/data/3307/my.cnf
[client]
#password       = your_password
port            = 3307
socket          = /mysql/data/3307/mysql.sock
default-character-set=utf8
# Here follows entries for some specific programs

# The MySQL server
[mysqld]
ignore-db-dir=lost+found
federated
character-set-server=utf8
collation-server=utf8_bin
port            = 3307
socket          = /mysql/data/3307/mysql.sock
datadir=/mysql/data/3307
skip-external-locking
lower-case-table-names=1
#MYISAM
max_connections=2048
key_buffer_size = 512M
max_allowed_packet = 16M
table_open_cache = 2048
sort_buffer_size = 8M
net_buffer_length = 512K
read_buffer_size = 8M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 256M
query_cache_size = 64M
join_buffer_size = 8M
thread_concurrency= 24
thread_cache_size = 8
query_cache_size= 0
query_cache_type= 0
slow_query_log= 1
long-query-time = 1
transaction_isolation = REPEATABLE-READ
bulk_insert_buffer_size = 64M
myisam_max_sort_file_size = 10G
skip-name-resolve
event_scheduler = ON
transaction-isolation=READ-COMMITTED
log_bin_trust_function_creators = 1
#skip-name-resolve

# binary logging is required for replication
log-bin=mysql-bin
expire_logs_days=3
#binlog_format=ROW
binlog_cache_size = 64M

server-id       = 1

innodb_data_home_dir = /mysql/data/3307
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /mysql/data/3307
innodb_buffer_pool_size = 200m
innodb_log_file_size = 500M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 20
innodb_io_capacity=2000
innodb_buffer_pool_instances= 3
innodb_purge_threads=1
innodb_stats_on_metadata=off
innodb_flush_method=O_DIRECT
innodb_file_per_table=true
innodb_print_all_deadlocks = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

大部分参数的功能我现在还不知道，这个参数是从一个会mysql的同事那拿到的，据说是通过压力测试后的参数配置。等以后会学习这些参数的作用，现在先过了。

修改目录权限，拥有者改成mysql。

[root@mysql-1 3307]# chown -R mysql:mysql /mysql

接下来就可以通过mysql_install_db脚本来初始化新的mysql数据库(实例)了。

[root@mysql-1 3307]# mysql_install_db --defaults-file=/mysql/data/3307/my.cnf --user=mysql --datadir=/mysql/data/3307
Installing MySQL system tables...2016-01-31 17:38:38 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
2016-01-31 17:38:38 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-01-31 17:38:38 30155 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-01-31 17:38:38 30155 [Note] InnoDB: The InnoDB memory heap is disabled
2016-01-31 17:38:38 30155 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-01-31 17:38:38 30155 [Note] InnoDB: Memory barrier is not used
2016-01-31 17:38:38 30155 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-01-31 17:38:38 30155 [Note] InnoDB: Using Linux native AIO
2016-01-31 17:38:38 30155 [Note] InnoDB: Using CPU crc32 instructions
2016-01-31 17:38:38 30155 [Note] InnoDB: Initializing buffer pool, size = 200.0M
2016-01-31 17:38:38 30155 [Note] InnoDB: Completed initialization of buffer pool
2016-01-31 17:38:38 30155 [Note] InnoDB: The first specified data file /mysql/data/3307/ibdata1 did not exist: a new database to be created!
2016-01-31 17:38:38 30155 [Note] InnoDB: Setting file /mysql/data/3307/ibdata1 size to 10 MB
2016-01-31 17:38:38 30155 [Note] InnoDB: Database physically writes the file full: wait...
2016-01-31 17:38:38 30155 [Note] InnoDB: Setting log file /mysql/data/3307/ib_logfile101 size to 500 MB
InnoDB: Progress in MB: 100 200 300 400 500
2016-01-31 17:38:42 30155 [Note] InnoDB: Setting log file /mysql/data/3307/ib_logfile1 size to 500 MB
InnoDB: Progress in MB: 100 200 300 400 500
2016-01-31 17:38:45 30155 [Note] InnoDB: Renaming log file /mysql/data/3307/ib_logfile101 to /mysql/data/3307/ib_logfile0
2016-01-31 17:38:45 30155 [Warning] InnoDB: New log files created, LSN=45781
2016-01-31 17:38:45 30155 [Note] InnoDB: Doublewrite buffer not found: creating new
2016-01-31 17:38:45 30155 [Note] InnoDB: Doublewrite buffer created
2016-01-31 17:38:45 30155 [Note] InnoDB: 128 rollback segment(s) are active.
2016-01-31 17:38:45 30155 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-01-31 17:38:45 30155 [Note] InnoDB: Foreign key constraint system tables created
2016-01-31 17:38:45 30155 [Note] InnoDB: Creating tablespace and datafile system tables.
2016-01-31 17:38:45 30155 [Note] InnoDB: Tablespace and datafile system tables created.
2016-01-31 17:38:45 30155 [Note] InnoDB: Waiting for purge to start
2016-01-31 17:38:45 30155 [Note] InnoDB: 5.6.21 started; log sequence number 0
2016-01-31 17:38:46 30155 [Note] Binlog end
2016-01-31 17:38:46 30155 [Note] InnoDB: FTS optimize thread exiting.
2016-01-31 17:38:46 30155 [Note] InnoDB: Starting shutdown...
2016-01-31 17:38:47 30155 [Note] InnoDB: Shutdown completed; log sequence number 1625984
OK

Filling help tables...2016-01-31 17:38:47 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
2016-01-31 17:38:47 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-01-31 17:38:47 30177 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-01-31 17:38:47 30177 [Note] InnoDB: The InnoDB memory heap is disabled
2016-01-31 17:38:47 30177 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-01-31 17:38:47 30177 [Note] InnoDB: Memory barrier is not used
2016-01-31 17:38:47 30177 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-01-31 17:38:47 30177 [Note] InnoDB: Using Linux native AIO
2016-01-31 17:38:47 30177 [Note] InnoDB: Using CPU crc32 instructions
2016-01-31 17:38:47 30177 [Note] InnoDB: Initializing buffer pool, size = 200.0M
2016-01-31 17:38:47 30177 [Note] InnoDB: Completed initialization of buffer pool
2016-01-31 17:38:47 30177 [Note] InnoDB: Highest supported file format is Barracuda.
2016-01-31 17:38:47 30177 [Note] InnoDB: 128 rollback segment(s) are active.
2016-01-31 17:38:47 30177 [Note] InnoDB: Waiting for purge to start
2016-01-31 17:38:47 30177 [Note] InnoDB: 5.6.21 started; log sequence number 1625984
2016-01-31 17:38:48 30177 [Note] Binlog end
2016-01-31 17:38:48 30177 [Note] InnoDB: FTS optimize thread exiting.
2016-01-31 17:38:48 30177 [Note] InnoDB: Starting shutdown...
2016-01-31 17:38:49 30177 [Note] InnoDB: Shutdown completed; log sequence number 1625994
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/bin/mysqladmin -u root password 'new-password'
  /usr/bin/mysqladmin -u root -h 10.9.15.201 password 'new-password'

Alternatively you can run:

  /usr/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 ; /usr/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/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/my-new.cnf,
please compare it with your file and take the changes you need.

初始化完成，最后的日志也能学到一些知识，下面分解一下。

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

这段日志的意思是说，如果想要自动启动mysql，可以把mysql.server文件拷贝到/etc/init.d/目录下，然后通过chkconfig命令添加到自动启动程序里面，下次服务器重启时会自动启动mysql服务。也可以使用service mysql.server start(或者/etc/init.d/mysql.server start)的方式启动。

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

  /usr/bin/mysqladmin -u root password 'new-password'
  /usr/bin/mysqladmin -u root -h 10.9.15.201 password 'new-password'

Alternatively you can run:

  /usr/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.

这段日志的意思是，要给mysql数据库的root用户设置密码，通过mysql_install_db工具初始化的数据库，默认root用户没有密码。这里提供mysqladmin工具来修改root用户的密码，也可以运行mysql_secure_installation脚本来修改，mysql_secure_installation脚本在上一篇文章(mysql安装-linux6.5通过rpm包方式安装mysql5.6.21)已经介绍，这里不在说明。

You can start the MySQL daemon with:

  cd /usr ; /usr/bin/mysqld_safe &

这段日志说的是可以用mysqld_safe工具来启动mysql数据库。

You can test the MySQL daemon with mysql-test-run.pl

  cd mysql-test ; perl mysql-test-run.pl

这段日志说的是mysql-test-run.pl测试脚本，可以通过这个脚本进行一些简单的测试。

WARNING: Found existing config file /usr/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/my-new.cnf,
please compare it with your file and take the changes you need.

这段日志说的是，在/usr目录下建立mysql的参数文件，因为之前通过rpm安装mysql的时候已经创建了一个，所以参数文件的名字为my-new.cnf，这个文件里面的内容和初始化的时候自己创建的my.cnf文件内容并不一样，一般不会用到这个文件。

下面启动新创建的mysql数据库。

[root@mysql-1 3307]# mysqld_safe --defaults-file=/mysql/data/3307/my.cnf &
[1] 30229
[root@mysql-1 3307]# 160131 17:45:48 mysqld_safe Logging to '/mysql/data/3307/mysql-1.err'.
160131 17:45:48 mysqld_safe Starting mysqld daemon with databases from /mysql/data/3307

启动时的日志信息可以通过mysql-1.err文件来查看，这个文件相当于oracle的告警日志文件。

[root@mysql-1 3307]# tail -100f mysql-1.err
160131 17:45:48 mysqld_safe Starting mysqld daemon with databases from /mysql/data/3307
2016-01-31 17:45:49 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
2016-01-31 17:45:49 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-01-31 17:45:49 30925 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-01-31 17:45:49 30925 [Note] InnoDB: The InnoDB memory heap is disabled
2016-01-31 17:45:49 30925 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-01-31 17:45:49 30925 [Note] InnoDB: Memory barrier is not used
2016-01-31 17:45:49 30925 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-01-31 17:45:49 30925 [Note] InnoDB: Using Linux native AIO
2016-01-31 17:45:49 30925 [Note] InnoDB: Using CPU crc32 instructions
2016-01-31 17:45:49 30925 [Note] InnoDB: Initializing buffer pool, size = 200.0M
2016-01-31 17:45:49 30925 [Note] InnoDB: Completed initialization of buffer pool
2016-01-31 17:45:49 30925 [Note] InnoDB: Highest supported file format is Barracuda.
2016-01-31 17:45:49 30925 [Note] InnoDB: 128 rollback segment(s) are active.
2016-01-31 17:45:49 30925 [Note] InnoDB: Waiting for purge to start
2016-01-31 17:45:49 30925 [Note] InnoDB: 5.6.21 started; log sequence number 1625994
2016-01-31 17:45:49 30925 [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: 68f9e3fb-c7ff-11e5-a5ad-000c29b29b65.
2016-01-31 17:45:49 30925 [Note] Server hostname (bind-address): '*'; port: 3307
2016-01-31 17:45:49 30925 [Note] IPv6 is available.
2016-01-31 17:45:49 30925 [Note]   - '::' resolves to '::';
2016-01-31 17:45:49 30925 [Note] Server socket created on IP: '::'.
2016-01-31 17:45:49 30925 [Warning] 'user' entry 'root@mysql-1' ignored in --skip-name-resolve mode.
2016-01-31 17:45:49 30925 [Warning] 'user' entry '@mysql-1' ignored in --skip-name-resolve mode.
2016-01-31 17:45:49 30925 [Warning] 'proxies_priv' entry '@ root@mysql-1' ignored in --skip-name-resolve mode.
2016-01-31 17:45:49 30925 [Note] Event Scheduler: Loaded 0 events
2016-01-31 17:45:49 30925 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.21-log'  socket: '/mysql/data/3307/mysql.sock'  port: 3307  MySQL Community Server (GPL)
2016-01-31 17:45:49 30925 [Note] Event Scheduler: scheduler thread started with id 1

也可以通过ps命令来查看mysql的启动的信息。

[root@mysql-1 3307]# ps -ef | grep mysql
root     12586     1  0 Jan28 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/mysql-1.pid
mysql    12687 12586  0 Jan28 ?        00:01:19 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/mysql-1.err --pid-file=/var/lib/mysql/mysql-1.pid
root     30229 30002  0 17:45 pts/1    00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/mysql/data/3307/my.cnf
mysql    30925 30229  0 17:45 pts/1    00:00:00 /usr/sbin/mysqld --defaults-file=/mysql/data/3307/my.cnf --basedir=/usr --datadir=/mysql/data/3307 --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/mysql/data/3307/mysql-1.err --pid-file=/mysql/data/3307/mysql-1.pid --socket=/mysql/data/3307/mysql.sock --port=3307
root     30956 30002  0 17:46 pts/1    00:00:00 grep mysql

此时就可以通过127.0.0.1这个IP登录mysql数据库了。

[mysql@mysql-1 ~]$ mysql -uroot -p -P3307 -h127.0.0.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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>

默认root还没密码，上文输入密码时，直接回车即可。

mysql> select host,user,password from mysql.user
    -> ;
+-----------+------+----------+
| host      | user | password |
+-----------+------+----------+
| localhost | root |          |
| mysql-1   | root |          |
| 127.0.0.1 | root |          |
| ::1       | root |          |
| localhost |      |          |
| mysql-1   |      |          |
+-----------+------+----------+
6 rows in set (0.00 sec)

有关mysql登录数据库权限相关的测试，下文再说。

本文固定链接: https://www.dbdream.com.cn/2016/03/%e4%bd%bf%e7%94%a8mysql_install_db%e8%84%9a%e6%9c%ac%e5%88%9d%e5%a7%8b%e5%8c%96mysql%e6%95%b0%e6%8d%ae%e5%ba%93%e5%ae%9e%e4%be%8b/ | 信春哥，系统稳，闭眼上线不回滚！