Return to Question

added 3366 characters in body

Source Link

edited Feb 28, 2024 at 6:00

Some related details:

chris@chris-X1C6:/var/log/mysql$ df -h
Filesystem      Size  Used Avail Use% Mounted on
tmpfs           1.6G  4.1M  1.6G   1% /run
/dev/nvme0n1p6  173G  153G   12G  93% /
tmpfs           7.7G   31M  7.7G   1% /dev/shm
tmpfs           5.0M  4.0K  5.0M   1% /run/lock
efivarfs        154K   52K   98K  35% /sys/firmware/efi/efivars
tmpfs           7.7G     0  7.7G   0% /run/qemu
/dev/nvme0n1p4   60G  769M   60G   2% /media/chris/Share
/dev/nvme0n1p1  256M   35M  222M  14% /boot/efi
/dev/sdc2       4.6T   36K  4.3T   1% /media/chris/F
/dev/sda1       932G  287G  646G  31% /media/chris/T7
/dev/sdb2       4.6T  4.3T  260G  95% /media/chris/E
tmpfs           1.6G   96K  1.6G   1% /run/user/1000


chris@chris-X1C6:/var/lib/mysql$ ll --ignore=binlog*
total 1790432
-rw-rw-rw-  1 mysql mysql      1709 Dec  5  2022  ca-key.pem
-rw-rw-rw-  1 mysql mysql      1112 Dec  5  2022  ca.pem
-rw-rw-rw-  1 mysql mysql      1705 Dec  5  2022  server-key.pem
-rw-rw-rw-  1 mysql mysql      1112 Dec  5  2022  server-cert.pem
-rw-rw-rw-  1 mysql mysql      1705 Dec  5  2022  client-key.pem
-rw-rw-rw-  1 mysql mysql      1112 Dec  5  2022  client-cert.pem
-rw-rw-rw-  1 mysql mysql      1705 Dec  5  2022  private_key.pem
-rw-rw-rw-  1 mysql mysql       452 Dec  5  2022  public_key.pem
drwxrwxrwx  2 mysql mysql      4096 Dec  5  2022  sys/
drwxrwxrwx  2 mysql mysql      4096 Dec 14  2022  fu/
drwxrwxrwx  2 mysql mysql      4096 Dec 21  2022  eq/
drwxrwxrwx  2 mysql mysql      4096 May  8  2023  performance_schema/
-rw-rw-rw-  1 mysql mysql         0 Jan 26 17:44  mysql.sock
-rw-rw-rw-  1 mysql mysql         0 Jan 26 17:44  mysql.pid
-rw-rw-rw-  1 mysql mysql         0 Jan 31 06:40  debian-5.7.flag
drwxrwxrwx  2 mysql mysql      4096 Jan 31 06:40  mysql/
-rw-rw-rw-  1 mysql mysql         6 Jan 31 06:40  mysql_upgrade_info
lrwxrwxrwx  1 root  root         14 Feb 13 22:31  F -> /media/chris/F/
drwxr-xr-x 83 root  root       4096 Feb 17 09:46  ../
-rw-rw-rw-  1 mysql mysql   8585216 Feb 20 17:00 '#ib_16384_1.dblwr'
lrwxrwxrwx  1 mysql mysql        23 Feb 20 19:27  op -> /media/chris/F/mysql/op/
-rw-r-----  1 mysql mysql        56 Feb 20 19:28  auto.cnf
-rw-r-----  1 mysql mysql      3272 Feb 21 11:22  ib_buffer_pool
drwxrwxrwx  2 mysql mysql      4096 Feb 22 14:31 '#innodb_temp'/
drwxrwxrwx  2 mysql mysql      4096 Feb 22 14:32 '#innodb_redo'/
-rw-r-----  1 mysql mysql         5 Feb 22 14:32  chris-X1C6.pid
-rw-r-----  1 mysql mysql  12582912 Feb 22 14:32  ibtmp1
-rw-rw-rw-  1 mysql mysql 855638016 Feb 22 15:31  undo_002
drwxrwxrwx  9 mysql mysql      4096 Feb 26 00:00  ./
-rw-rw-rw-  1 mysql mysql 838860800 Feb 26 13:37  undo_001
-rw-rw-rw-  1 mysql mysql  79691776 Feb 26 13:37  ibdata1
-rw-rw-rw-  1 mysql mysql    196608 Feb 26 13:37 '#ib_16384_0.dblwr'
-rw-rw-rw-  1 mysql mysql  37748736 Feb 26 13:37  mysql.ibd

chris@chris-X1C6:/var/lib/mysql$ ls -lad /media/chris/F/mysql/op /var/lib/mysql/op
drwxrwxrwx 2 mysql mysql 4096 Feb 20 18:34 /media/chris/F/mysql/op
lrwxrwxrwx 1 mysql mysql   23 Feb 20 19:27 /var/lib/mysql/op -> /media/chris/F/mysql/op

Some related details:

chris@chris-X1C6:/var/log/mysql$ df -h
Filesystem      Size  Used Avail Use% Mounted on
tmpfs           1.6G  4.1M  1.6G   1% /run
/dev/nvme0n1p6  173G  153G   12G  93% /
tmpfs           7.7G   31M  7.7G   1% /dev/shm
tmpfs           5.0M  4.0K  5.0M   1% /run/lock
efivarfs        154K   52K   98K  35% /sys/firmware/efi/efivars
tmpfs           7.7G     0  7.7G   0% /run/qemu
/dev/nvme0n1p4   60G  769M   60G   2% /media/chris/Share
/dev/nvme0n1p1  256M   35M  222M  14% /boot/efi
/dev/sdc2       4.6T   36K  4.3T   1% /media/chris/F
/dev/sda1       932G  287G  646G  31% /media/chris/T7
/dev/sdb2       4.6T  4.3T  260G  95% /media/chris/E
tmpfs           1.6G   96K  1.6G   1% /run/user/1000


chris@chris-X1C6:/var/lib/mysql$ ll --ignore=binlog*
total 1790432
-rw-rw-rw-  1 mysql mysql      1709 Dec  5  2022  ca-key.pem
-rw-rw-rw-  1 mysql mysql      1112 Dec  5  2022  ca.pem
-rw-rw-rw-  1 mysql mysql      1705 Dec  5  2022  server-key.pem
-rw-rw-rw-  1 mysql mysql      1112 Dec  5  2022  server-cert.pem
-rw-rw-rw-  1 mysql mysql      1705 Dec  5  2022  client-key.pem
-rw-rw-rw-  1 mysql mysql      1112 Dec  5  2022  client-cert.pem
-rw-rw-rw-  1 mysql mysql      1705 Dec  5  2022  private_key.pem
-rw-rw-rw-  1 mysql mysql       452 Dec  5  2022  public_key.pem
drwxrwxrwx  2 mysql mysql      4096 Dec  5  2022  sys/
drwxrwxrwx  2 mysql mysql      4096 Dec 14  2022  fu/
drwxrwxrwx  2 mysql mysql      4096 Dec 21  2022  eq/
drwxrwxrwx  2 mysql mysql      4096 May  8  2023  performance_schema/
-rw-rw-rw-  1 mysql mysql         0 Jan 26 17:44  mysql.sock
-rw-rw-rw-  1 mysql mysql         0 Jan 26 17:44  mysql.pid
-rw-rw-rw-  1 mysql mysql         0 Jan 31 06:40  debian-5.7.flag
drwxrwxrwx  2 mysql mysql      4096 Jan 31 06:40  mysql/
-rw-rw-rw-  1 mysql mysql         6 Jan 31 06:40  mysql_upgrade_info
lrwxrwxrwx  1 root  root         14 Feb 13 22:31  F -> /media/chris/F/
drwxr-xr-x 83 root  root       4096 Feb 17 09:46  ../
-rw-rw-rw-  1 mysql mysql   8585216 Feb 20 17:00 '#ib_16384_1.dblwr'
lrwxrwxrwx  1 mysql mysql        23 Feb 20 19:27  op -> /media/chris/F/mysql/op/
-rw-r-----  1 mysql mysql        56 Feb 20 19:28  auto.cnf
-rw-r-----  1 mysql mysql      3272 Feb 21 11:22  ib_buffer_pool
drwxrwxrwx  2 mysql mysql      4096 Feb 22 14:31 '#innodb_temp'/
drwxrwxrwx  2 mysql mysql      4096 Feb 22 14:32 '#innodb_redo'/
-rw-r-----  1 mysql mysql         5 Feb 22 14:32  chris-X1C6.pid
-rw-r-----  1 mysql mysql  12582912 Feb 22 14:32  ibtmp1
-rw-rw-rw-  1 mysql mysql 855638016 Feb 22 15:31  undo_002
drwxrwxrwx  9 mysql mysql      4096 Feb 26 00:00  ./
-rw-rw-rw-  1 mysql mysql 838860800 Feb 26 13:37  undo_001
-rw-rw-rw-  1 mysql mysql  79691776 Feb 26 13:37  ibdata1
-rw-rw-rw-  1 mysql mysql    196608 Feb 26 13:37 '#ib_16384_0.dblwr'
-rw-rw-rw-  1 mysql mysql  37748736 Feb 26 13:37  mysql.ibd

chris@chris-X1C6:/var/lib/mysql$ ls -lad /media/chris/F/mysql/op /var/lib/mysql/op
drwxrwxrwx 2 mysql mysql 4096 Feb 20 18:34 /media/chris/F/mysql/op
lrwxrwxrwx 1 mysql mysql   23 Feb 20 19:27 /var/lib/mysql/op -> /media/chris/F/mysql/op

Source Link

asked Feb 28, 2024 at 0:33

Chris

Can I use symbolic links for a MySQL database

I have a MySQL installation on my machine where I need to add a very large table that will max out existing capacity.

I've previously housed MySQL data on external drives but I'd prefer to keep as much as possible on my laptop hard drive rather than an external HD due to read/write concerns, necessitating a symbolic link vs just moving all data to the HD.

I referenced MySQL documentation to do this, so it seems like it should work but nothing I've done allows me to (first) create a table on this new database (would ultimately like to import a bulk export .sql file/table to this new DB).

Getting:

mysql> create table test(`test` varchar(8) NOT NULL);
    ERROR 1030 (HY000): Got error 168 - 'Unknown (generic) error from engine' from storage engine

Error log isn't showing much, just this warning:

chris@chris-X1C6:/var/log/mysql$ cat error.log
2024-02-26T21:36:16.697730Z 19 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'

I've checked that symbolic links are turned on in MySQL 8, I've checked mysql is owner of all applicable directories, and that there's plenty of space in each location, but I still get this error 168. What am I missing? Running MySQL 8.0 on Ubuntu 22.04 if it matters.

mysql