MySQL – How to Reinitialize /var/lib/mysql Files on Debian

debianlinuxMySQL

Due to a mishap I deleted the entire /var/lib/mysql directory. Since the database did not contain anything important, I do not want to go through the hassle of restoring it from an old backup, but instead create the directory structure from scratch. How do I do it without reinstalling MySQL?

Best Answer

You should be able to just run mysql_install_db from the command line

mysql_install_db initializes the MySQL data directory and creates the system tables that it contains, if they do not exist. It also initializes the system tablespace and related data structures needed to manage InnoDB tables. As of MySQL 5.6.8, mysql_install_db is a Perl script and can be used on any system with Perl installed. Before 5.6.8, it is a shell script and is available only on Unix platforms.

Please read the MySQL Documentation in full for mysql_install_db, then ...

GIVE IT A TRY !!!

If it does not work on the first try, do this

mkdir /var/lib/mysql
mkdir /var/lib/mysql/mysql
chown -R mysql:mysql /var/lib/mysql

then run mysql_install_db again

Related Solutions

MySQL – How to Recover Missing Data Files

OK here is your situation: your INNODB_FILE_PER_TABLE is OFF.

The data and index pages for the InnoDB tables are all inside the system tablespace (ibdata1). For those tables, you only see the .frm files. Therefore, everything is OK.

Here is what InnoDB looks like (image courtesy of Percona CTO Vadim Tkachenko)

InnoDB Architecture

These days, many use the default innodb_file_per_table = 1. This used to be 0 for the default back in the days of MySQL 5.1. If you have innodb_file_per_table = 0 in your my.cnf, then MySQL is leaving all InnoDB tables inside ibdata1.

For more info on this, see my posts

You can leave the InnoDB tables inside ibdata1. If you want them stored outside, see the CAVEAT of my post How can I determine which Innodb table is being written?

Mysql – How to restore database from /var/lib/thesql

you need to make sure the ownership and privileges are correct for the directory, you can use the following commands to set it right:

chown -R mysql:mysql /var/lib/mysql/ --your mysql user may have different name, if its different you need to change it [mysql:mysql] as needed 
chmod -R 755 /var/lib/mysql/

note: -R: recursive fir the directory and all its contents.

Best Answer

GIVE IT A TRY !!!

Related Solutions

MySQL – How to Recover Missing Data Files

Mysql – How to restore database from /var/lib/thesql

Related Question