Make MySQL Server Run Startup Script via init-file Configuration Option Successfully!

AmazonAre you trying to instruct your MySQL server to run a startup SQL script whenever the server boots up? If so you may encounter many strange issues along the way. I'll walk through solving and troubleshooting each of them.

Read on to learn how to make MySQL startup script work.

Summary

Basically we'll create a MySQL script and use the init_file configuration option to tell MySQL server, or mysqld, to run this script every time it boots up. As easy as it sounds you may encounter many setbacks that are in store for you. Don't worry. I've managed to make it work and I am walking you through the whole thing step by step.

Export your database table

First you need to decide what SQL statements you want to execute at server bootup. A common case is populate a database table with preset data. Let me walk you through an example.

You need to obey all the rules of the syntax and format of the MySQL script indicated by the init_file location. The biggest rule is every SQL command needs to be on its own line.

Suppose myDb database has three tables: article, entry, entry_memory. We'd like to export the table entry_memory to /home/ubuntu/schema/myDb.entry_memory.sql. The following line would export a table's data in the format that's acceptable by init_file, assuming user name is user and password is password.

mysqldump -uuser -ppassword --extended-insert=FALSE --no-create-info --skip-comments --databases myDb --ignore-table=myDb.article --ignore-table=myDb.entry > /home/ubuntu/schema/myDb.entry_memory.sql

Why do we need to use --databases and --ignore-table? It is because I need to include 'use myDb' in the beginning of the script which is what --databases does, but if I have --databases mysqldump will dump all tables in myDb database. Therefore I need to use --ignore-table to ignore the tables I don't want.

If you have a better idea feel free to chime in!

Modify MySQL my.cnf configuration

Let's modify MySQL's configuration. My my.cnf is located at /etc/mysql/my.cnf. Add the following line under [mysqld].

...
[mysqld]
init_file=/home/ubuntu/repository201207/chtoen/protected/schema/chtoen.entry_memory.sql
...

Restart MySQL daemon and you are done! I execute the following command to restart my MySQL server.

sudo service mysql restart

If you see errors continue reading for tips on solving them.

Restarting MySQL hangs..

If starting up MySQL hangs without finishing, check MySQL's error log which is set by log_error in my.cnf. My error log is located at /var/log/mysql/error.log. The error says:

130829 [ERROR] /usr/sbin/mysqld: File '/home/ubuntu/schema/myDb.entry_memory.sql' not found (Errcode: 13)
130829 [ERROR] Aborting

So that's the error I need to fix. Here's how you fix it.

1. Edit the mysql apparmor file:

sudo vi /etc/apparmor.d/usr.sbin.mysqld

2. Include the script with 'r' permission. If you want you can include the folder where your multiple scripts live with the *.sql extension:

...
  /etc/mysql/*.pem r,
  /etc/mysql/conf.d/ r,
  /etc/mysql/conf.d/* r,
  /etc/mysql/my.cnf r,
  /usr/sbin/mysqld mr,
  /usr/share/mysql/** r,
  /var/log/mysql.log rw,
  /var/log/mysql.err rw,
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,
  /var/log/mysql/ r,
  /var/log/mysql/* rw,
  /var/run/mysqld/mysqld.pid w,
  /var/run/mysqld/mysqld.sock w,

  /sys/devices/system/cpu/ r,

  # I added to allow my init-file script to run
  /home/ubuntu/schema/myDb.entry_memory.sql r,
}

Restart your MySQL daemon and it should execute /home/ubuntu/schema/myDb.entry_memory.sql successfully!

If you have any questions let me know and I will do my best to help you!