Mysql on tmpfs with read-only rootfs

by user3490458   Last Updated May 07, 2017 10:01 AM

I would like to mount my rootfs as read-only & /var/lib/mysql, /var/tmp, /var/log & /tmp as tmpfs.

I wan't to be able to minimize writes to the fs which is on a SD card. So I need to keep my rootfs read-only. I also want to be able to keep open the option to persist my mysql changes (at poweroff).

  1. The problem I'm currently facing is that I'm not able to get mysql to run with /var/lib/mysql mounted on tmpfs.

And here's the error I'm getting:

# cat /var/log/mysql/error.log
170507 12:57:53 mysqld_safe Logging to '/var/log/mysql/error.log'.
170507 12:57:54 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
170507 12:57:54 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
170507 12:57:54 [Note] /usr/sbin/mysqld (mysqld 5.5.55-0+deb8u1) starting as process 1179 ...
170507 12:57:54 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
170507 12:57:54 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Table 'mysql.plugin' doesn't exist
170507 12:57:54 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
170507 12:57:54 InnoDB: The InnoDB memory heap is disabled
170507 12:57:54 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170507 12:57:54 InnoDB: Compressed tables use zlib 1.2.8
170507 12:57:54 InnoDB: Using Linux native AIO
170507 12:57:54 InnoDB: Initializing buffer pool, size = 128.0M
170507 12:57:54 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
170507 12:57:54  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
170507 12:57:54  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
170507 12:57:54  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: 127 rollback segment(s) active.
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
170507 12:57:55  InnoDB: Waiting for the background threads to start
170507 12:57:56 InnoDB: 5.5.55 started; log sequence number 0
170507 12:57:56 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
170507 12:57:56 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
170507 12:57:56 [Note] Server socket created on IP: '127.0.0.1'.
170507 12:57:56 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
170507 12:57:56 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Here's my fstab:

# cat /etc/fstab
/dev/mmcblk0p1 / ext4 ro,defaults,noatime,nodiratime,commit=600,errors=remount-ro 0 1
tmpfs /tmp tmpfs defaults,nosuid 0 0
/var/swap none swap sw 0 0
tmpfs /var/tmp tmpfs nodev,nosuid,size=50M 0 0
tmpfs /var/log tmpfs nodev,nosuid,size=500M 0 0
tmpfs /var/lib/mysql tmpfs nodev,nosuid,gid=114,uid=107,size=100M,nr_inodes=10k,mode=0700 0 0
  1. I also need to know how I can get mysql to use the persisted data after a reboot.


Related Questions


Can I expand the size of a file based disk image?

Updated September 20, 2015 21:00 PM

Mount image of unknown filesystem

Updated August 24, 2015 19:00 PM

How to mount read/write with pmount?

Updated December 30, 2017 19:01 PM