Edit article

Title Tags Content

<pre><code class="language-bash"># https://rtcamp.com/tutorials/mysql/enable-innodb-file-per-table/

# if mysql &gt; 5.6.6
# backup
# ...
# For better results, shut down PHP and other apps/scripts which update mysql. You can keep Nginx running and server non-logged in visitors cached content.
service mysql stop &amp;&amp; cp -ra /var/lib/mysql mysqldata &amp;&amp; service mysql start
# As soon as above line completes, take a mysqldump of all databases
mysqldump --routines --events --flush-privileges --all-databases &gt; all-db.sql
# Create a sql file to drop all databases EXCEPT mysql database
mysql -e "SELECT DISTINCT CONCAT ('DROP DATABASE ',TABLE_SCHEMA,' ;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA &lt;&gt; 'mysql' AND TABLE_SCHEMA &lt;&gt; 'information_schema';" | tail -n+2 &gt; drop.sql
# Verify if drop.sql has correct database names and then execute drop.sql queries.
mysql &lt; drop.sql
# Verify all InnoDB tables gone
SELECT table_name, table_schema, engine FROM information_schema.tables WHERE engine = 'InnoDB';
# Stop mysql server first
service mysql stop
rm /var/lib/mysql/ibdata1 &amp;&amp; rm /var/lib/mysql/ib_logfile0 &amp;&amp; rm /var/lib/mysql/ib_logfile1

# Enable innodb_file_per_table
cat &gt;&gt; /etc/mysql/my.cnf &lt;&lt;"EOF"
innodb_file_per_table = 1
innodb_file_format = barracuda
EOF

# Start mysql server now
service mysql start
# Run mysql import
mysql &lt; all-db.sql
# Force mysql_upgrade (to generate performance_schema)
mysql_upgrade --force

### Alternative:

#!/bin/bash -x

if test "$EUID" -ne 0 ; then
	echo "Must be root ..."
	exit 1
fi

mysql_dba=root
mysql_pass=your_mysql_dba_password

mkdir /var/lib/mysql.bak
cd /var/lib/mysql.bak
service mysql stop &amp;&amp; cp -ra /var/lib/mysql mysqldata &amp;&amp; service mysql start
mysqldump -u$mysql_dba -p$mysql_pass --routines --events --flush-privileges --all-databases &gt; all-db.sql
mysql -u$mysql_dba -p$mysql_pass -e "SELECT DISTINCT CONCAT ('DROP DATABASE ',TABLE_SCHEMA,' ;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA &lt;&gt; 'mysql' AND TABLE_SCHEMA &lt;&gt; 'information_schema';" | tail -n+2 &gt; drop.sql
cat drop.sql
mysql -u$mysql_dba -p$mysql_pass &lt; drop.sql
echo "SELECT table_name, table_schema, engine FROM information_schema.tables WHERE engine = 'InnoDB';" &gt; innotables.sql
mysql -u$mysql_dba -p &lt; innotables.sql
service mysql stop
rm /var/lib/mysql/ibdata1 &amp;&amp; rm /var/lib/mysql/ib_logfile0 &amp;&amp; rm /var/lib/mysql/ib_logfile1
cp -p /etc/mysql/my.cnf /etc/mysql/my.cnf.
date +%Y.%m.%d.%H.%M.%S

sed -e 's/([mysqld])/\1
innodb_file_per_table = 1
innodb_file_format = barracuda /' /etc/mysql/my.cnf &gt; /tmp/my.cnf
mv /tmp/my.cnf /etc/mysql/my.cnf
service mysql start
mysql -u$mysql_dba -p$mysql_pass &lt; all-db.sql
mysql_upgrade -u$mysql_dba -p$mysql_pass --force
# eof</code></pre>

Cancel