Sometimes you want to delete the mysql data folder. But the mysql server will not start after removing because of missing default tables and files. For this reason mysql offers a script mysql_install_db to create all necessary folders.
Create mysql data folder
We will stop mysql service, remove data folder and create a new one.
|
1 2 3 4 5 6 |
# stop mysql service sudo service mysql stop # remove old folder sudo rm -r /var/lib/mysql # create a new fresh folder sudo mysql_install_db |
Possible output
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
Installing MySQL system tables... 151230 17:02:47 [Note] /usr/sbin/mysqld (mysqld 5.5.46-0ubuntu0.14.04.2) starting as process 18114 ... OK Filling help tables... 151230 17:02:48 [Note] /usr/sbin/mysqld (mysqld 5.5.46-0ubuntu0.14.04.2) starting as process 18120 ... 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 schroedingerdb 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 /usr/mysql-test ; perl mysql-test-run.pl Please report any problems at http://bugs.mysql.com/ |
Using parameter defaults_file
In some cases your mysql installation is not default and you want to use a special config with other parameters or you have multiple mysql instances running. In this case you can run mysql_install_db with the parameter defaults_file.
|
1 |
sudo mysql_install_db --defaults-file=/etc/mysql-1/my.cnf |
Issue on ubuntu with mysql 5.5
On ubuntu 14.04 and with mysql 5.5 I get this error:
Could not open required defaults file: /etc/mysql-1/my.cnf
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
Installing MySQL system tables... Could not open required defaults file: /etc/mysql-1/my.cnf Fatal error in defaults handling. Program aborted Installation of system tables failed! Examine the logs in /var/lib/mysql-1 for more information. You can try to start the mysqld daemon with: shell> /usr/sbin/mysqld --skip-grant & and use the command line tool /usr/bin/mysql to connect to the mysql database and look at the grant tables: shell> /usr/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-1 that may be helpful. 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 submitting a bug report at http://bugs.mysql.com/ |
Stopping apparmor will solve this issue.
|
1 |
sudo service apparmor teardown |
Run mysql_install_db again.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
mysql_install_db --defaults-file=/etc/mysql-1/my.cnf Installing MySQL system tables... 151230 17:16:59 [Note] /usr/sbin/mysqld (mysqld 5.5.46-0ubuntu0.14.04.2) starting as process 18457 ... OK Filling help tables... 151230 17:16:59 [Note] /usr/sbin/mysqld (mysqld 5.5.46-0ubuntu0.14.04.2) starting as process 18463 ... 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 schroedingerdb 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 /usr/mysql-test ; perl mysql-test-run.pl Please report any problems at http://bugs.mysql.com/ |
Start apparmor again.
|
1 |
sudo service apparmor start |
Issue on ubuntu with mysql 5.6
I get this error:
|
1 2 3 4 5 6 7 8 |
FATAL ERROR: Could not find my-default.cnf If you compiled from source, you need to run 'make install' to copy the software into the correct location ready for operation. If you are using a binary release, you must either be at the top level of the extracted archive, or pass the --basedir option pointing to that location. |
There is one file missing under /usr/share/mysql/my-default.cnf. Copy your my.cnf in place of this file.
|
1 |
cp /etc/mysql/my.cnf /usr/share/mysql/my-default.cnf |
Output
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
Installing MySQL system tables...2016-01-03 18:05:42 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2016-01-03 18:05:42 0 [Note] /usr/sbin/mysqld (mysqld 5.6.27-0ubuntu0.14.04.1) starting as process 25467 ... OK Filling help tables...2016-01-03 18:05:46 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2016-01-03 18:05:46 0 [Note] /usr/sbin/mysqld (mysqld 5.6.27-0ubuntu0.14.04.1) starting as process 25490 ... 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 schroedingerdb 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. WARNING: Default config file /etc/mysql/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 |