0

I have an existing MySQL 8 installation on a Ubuntu 22.04 system that relies on the standard /var/lib/mysql datadir.

I'm trying to load a dumped .sql file to a new database, but it's too big to be loaded to the current datadir; I'm trying to use a symbolic link to house the data on a larger external drive instead.

I've turned on symlinks (which are disabled by default):

mysql> show variables like '%symlink%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_symlink  | YES   |
+---------------+-------+
1 row in set (0.01 sec)

I've created the database:

mysql> create database symop;
Query OK, 1 row affected (0.02 sec)

I've established the symlink:

chris@chris-X1C6:/media/chris/T7$ sudo ln -s /media/chris/F/symop /var/lib/mysql/symop

And started the load:

chris@chris-X1C6:/media/chris/T7$ mysql -h 127.0.0.1 -u root -p -D symop < op_prices.sql

but the size of the table within /var/lib/mysql/symop is the only one growing in size.

What am I missing?

Edit:

chris@chris-X1C6:/media/chris/F/symop$ sudo ls -lad /media/chris/F/symop /var/lib/mysql/symop
[sudo] password for chris:
drwxrwxrwx 1 root  root     0 Feb 13 22:27 /media/chris/F/symop
drwxr-x--- 2 mysql mysql 4096 Feb 13 23:18 /var/lib/mysql/symop
Chris
  • 191
  • 2
  • 11
  • "What am I missing?" Quite a lot, actually. Symlinks only work for MyISAM tables, and they are for each data and index file, not the database as a whole. Check the docs again. – mustaccio Feb 14 '24 at 18:30
  • @mustaccio, zinger, but how do you square that with this: https://dba.stackexchange.com/questions/14875/how-to-set-a-specific-directory-location-for-individual-mysql-database/14876#14876 – Chris Feb 14 '24 at 19:21
  • Read the docs here - https://dev.mysql.com/doc/refman/8.0/en/symbolic-links.html, basically states don't use symlinks for InnoDB tables and even added to use this with MyISAM as a last resort. – jerichorivera Feb 14 '24 at 22:05
  • 1
    That being said use general-tablespaces here - https://dev.mysql.com/doc/refman/8.0/en/general-tablespaces.html. This is going to be tricky when backing up since your best option is mysql-shell data dump/load and any physical backups could become a mess. – jerichorivera Feb 14 '24 at 22:07
  • 1
    Please provide ls -lad /media/chris/F/symop /var/lib/mysql/symop – Rick James Feb 14 '24 at 22:21
  • @RickJames, thanks, Q updated w output. External drive is formatted as an ntfs and won't take ownership changes...could this be as simple as formatting to, say ext3 and changing ownership to 'mysql'? – Chris Feb 15 '24 at 17:44
  • @jerichorivera, thanks for that. Had seen symlinks have been deprecated for MySQL10--not being best practice is different than not working though. Gonna take a closer look at tablespaces, thanks. – Chris Feb 15 '24 at 17:47
  • Edit to above: ntfs can take ownership changes as part of mount definition in fstab, but not post-mount via command line--while ext3 would allow use of chown – Chris Feb 15 '24 at 17:58
  • I don't see anything about deprecation; do you have a link to the web page saying that? Meanwhile, I don't see how general tablespaces help any. – Rick James Feb 15 '24 at 22:01
  • @RickJames, https://dev.mysql.com/worklog/task/?id=8392 – Chris Feb 15 '24 at 22:07

0 Answers0