Mysql5.7.6初始化数据库

有时需要废掉数据库里面的数据,重新初始化数据库,比如我们要搭建一个从数据库,但又不想重新安装数据库软件怎么办?步骤如下:

 

The procedure described here is available for all platforms as of MySQL 5.7.6. Prior to 5.7.6, use mysql_install_db on Unix and Unix-like systems. Prior to MySQL 5.7.7, Windows distributions include a data

directory with prebuilt tables in the mysql database.

该篇文章适用于MySQL 5.7.6及以后的版本;MySQL 5.7.6 UNIX及UNIX-LIKE系统版本需要使用mysql_install_db命令,MySQL 5.7.6 Windows系统版本在data目录在mysql数据库里包含一些预建的表。

1、先清空data目录

以下命令得知数据目录为/var/lib/mysql

[root@RAC1 ~]# ps -ef | grep mysql
root     10379     1  0 00:59 pts/0    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql    10625 10379  0 00:59 pts/0    00:00:36 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
[root@RAC1 mysql]# rm -rf *
[root@RAC1 mysql]# ls
[root@RAC1 mysql]# 

2、初始化数据库

[root@RAC1 mysql]# mysqld --initialize
[root@RAC1 mysql]# ls
auto.cnf  ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql_bin.000001  mysql_bin.index  performance_schema  sys
[root@RAC1 mysql]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
MySQL Daemon failed to start.
Starting mysqld:                                           [FAILED]
[root@RAC1 mysql]# 

3、排查问题

[root@RAC1 mysql]# tail /var/log/mysqld.log
2017-01-01T07:01:58.933959Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2017-01-01T07:01:59.092897Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-01-01T07:01:59.093610Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.12-log) starting as process 13767 ...
mysqld: File './mysql_bin.index' not found (Errcode: 13 - Permission denied)
2017-01-01T07:01:59.096502Z 0 [ERROR] Aborting

2017-01-01T07:01:59.096511Z 0 [Note] Binlog end
2017-01-01T07:01:59.096550Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

2017-01-01T07:01:59.103681Z mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
[root@RAC1 mysql]# ll
total 110728
-rw-r-----. 1 root root       56 Jan  1 15:01 auto.cnf
-rw-r-----. 1 root root      413 Jan  1 15:01 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Jan  1 15:01 ibdata1
-rw-r-----. 1 root root 50331648 Jan  1 15:01 ib_logfile0
-rw-r-----. 1 root root 50331648 Jan  1 15:01 ib_logfile1
drwxr-x---. 2 root root     4096 Jan  1 15:01 mysql
-rw-r-----. 1 root root    99537 Jan  1 15:01 mysql_bin.000001
-rw-r-----. 1 root root       19 Jan  1 15:01 mysql_bin.index
drwxr-x---. 2 root root     4096 Jan  1 15:01 performance_schema
drwxr-x---. 2 root root    12288 Jan  1 15:01 sys

[root@RAC1 mysql]# chown -R mysql:mysql ./*
[root@RAC1 mysql]# ll
total 110728
-rw-r-----. 1 mysql mysql       56 Jan  1 15:01 auto.cnf
-rw-r-----. 1 mysql mysql      413 Jan  1 15:01 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jan  1 15:01 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jan  1 15:01 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jan  1 15:01 ib_logfile1
drwxr-x---. 2 mysql mysql     4096 Jan  1 15:01 mysql
-rw-r-----. 1 mysql mysql    99537 Jan  1 15:01 mysql_bin.000001
-rw-r-----. 1 mysql mysql       19 Jan  1 15:01 mysql_bin.index
drwxr-x---. 2 mysql mysql     4096 Jan  1 15:01 performance_schema
drwxr-x---. 2 mysql mysql    12288 Jan  1 15:01 sys
[root@RAC1 mysql]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@RAC1 mysql]# 

4、登录数据库

mysqld --initialize命令会在日志文件中生成一个随机的已过期的密码

[root@RAC1 mysql]# cat /var/log/mysqld.log 
2017-01-01T07:01:18.686563Z 0 [Warning] InnoDB: New log files created, LSN=45790
2017-01-01T07:01:20.750101Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-01-01T07:01:21.268544Z 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: 19c2ef71-cff0-11e6-9ef5-080027df7c21.
2017-01-01T07:01:21.271012Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-01-01T07:01:21.273913Z 1 [Note] A temporary password is generated for root@localhost: w,Hn.VMsh4Sj
[root@RAC1 mysql]#
[root@RAC1 mysql]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.7.12-log
Copyright (c) 2000, 2016, 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> 
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.03 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)


OK,数据库初始完毕!

0

热门评论