Recently I installed MySQL, version 8.4.3, on my Ubuntu 22.04.5 OS. I then changed the datadir from /var/lib/mysql to new location /media/cat/hekla/mysql, following the approach described in Ergest Basha's answer to the question [Error after changing/moving mysql datadir in ubuntu sys](https://dba.stackexchange.com/questions/327307/error-after-changing-moving-mysql-datadir-in-ubuntu-sys) . Restarting MySQL however fails, and systemctl status mysql.service tells me: × mysql.service - MySQL Community Server Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled) Active: failed (Result: exit-code) since Wed 2024-11-06 11:18:27 CET; 2min 31s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 11628 ExecStartPre=/usr/share/mysql-8.4/mysql-systemd-start pre (code=exited, status=0/SUCCESS) Process: 11667 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE) Main PID: 11667 (code=exited, status=1/FAILURE) Status: "Server shutdown complete (with return value = 1)" Error: 13 (Permission denied) CPU: 292ms nov 06 11:18:27 golem mysqld: 2024-11-06T10:18:27.019616Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.4.3) starting as process 11667 nov 06 11:18:27 golem mysqld: 2024-11-06T10:18:27.021198Z 0 [Warning] [MY-010091] [Server] Can't create test file /media/cat/hekla/mysql/mysqld_tmp_file_case_insensitive_test.lower-test nov 06 11:18:27 golem mysqld: 2024-11-06T10:18:27.021202Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /media/cat/hekla/mysql/ is case insensitive nov 06 11:18:27 golem mysqld: 2024-11-06T10:18:27.021216Z 0 [ERROR] [MY-013276] [Server] Failed to set datadir to '/media/cat/hekla/mysql/' (OS errno: 13 - Permission denied) nov 06 11:18:27 golem mysqld: 2024-11-06T10:18:27.021275Z 0 [ERROR] [MY-010119] [Server] Aborting nov 06 11:18:27 golem mysqld: 2024-11-06T10:18:27.021685Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.4.3) MySQL Community Server - GPL. nov 06 11:18:27 golem mysqld: 2024-11-06T10:18:27.021689Z 0 [System] [MY-015016] [Server] MySQL Server - end. nov 06 11:18:27 golem systemd: mysql.service: Main process exited, code=exited, status=1/FAILURE nov 06 11:18:27 golem systemd: mysql.service: Failed with result 'exit-code'. nov 06 11:18:27 golem systemd: Failed to start MySQL Community Server. Interestingly, I made a backup copy /var/lib/mysql.bak of /var/lib/mysql, and when I change the datadir to this backup directory then it does work! Any ideas what the problem can be, and how to solve it? Any other things that could require adjustment, perhaps involving location /var/lib/ considering that the backup directory sitting next to the original does work? The content of /etc/apparmor.d/usr.sbin.mysqld is: include /usr/sbin/mysqld { #include #include #include #include #include # Allow system resource access /sys/devices/system/cpu/ r, /sys/devices/system/node/ r, /sys/devices/system/node/** r, /proc/*/status r, capability sys_resource, capability dac_override, capability setuid, capability setgid, capability sys_nice, # Allow network access network tcp, /etc/hosts.allow r, /etc/hosts.deny r, # Allow config access /etc/mysql/** r, # Allow pid, socket, socket lock and other file access /run/mysqld/* rw, /var/run/mysqld/* rw, # Allow systemd notify messages /{,var/}run/systemd/notify w, # Allow execution of server binary /usr/sbin/mysqld mr, /usr/sbin/mysqld-debug mr, # Allow plugin access /usr/lib/mysql/plugin/ r, /usr/lib/mysql/plugin/*.so* mr, # Allow error msg and charset access /usr/share/mysql/ r, /usr/share/mysql/** r, /usr/share/mysql-8.4/ r, /usr/share/mysql-8.4/** r, # Allow data dir access /var/lib/mysql/ r, /var/lib/mysql/** rwk, # /var/lib/mysql.bak/ r, # /var/lib/mysql.bak/** rwk, # /media/cat/hekla/mysql/ r, # /media/cat/hekla/mysql/** rwk, # Allow data files dir access /var/lib/mysql-files/ r, /var/lib/mysql-files/** rwk, # Allow keyring dir access /var/lib/mysql-keyring/ r, /var/lib/mysql-keyring/** rwk, # Allow log file access /var/log/mysql/ r, /var/log/mysql/** rw, # Allow access to openssl config /etc/ssl/openssl.cnf r, # Site-specific additions and overrides. See local/README for details. #include }

I have installed mysql server 5.6.24 on Windows Server 2012 R2. I imported dump files and the data files are stored in C:\ProgramData\MySQL\MySQL Server 5.6\data I want to change directory like D:\ProgramData\Data. Can you please help me, anyone ?

Long story short. I have a MySQL database that has only one table and is around 50GB, while my drive storage is almost full and cannot get reduced. So the only way is to reduce the size of the 50GB MySQL data table (the app I use is MySQL Workbench). I tried the Delete function but it does not work becase it requires additional storage (which I don't have) to perform the Optimize function so that the underlying IBD file can be truly reduced. I tried copying the IBD file to a different drive in the same computer and reopening it there using MySQL but it does not work as it seems opening an IBD file is not straight forward and very complicated based on my online research. In addition, then I tried changing MySQL's folder to a different drive path with the IBD file saved there by editing the my.ini file but the app cannot work after the change of folder path. So, is there any workaround such as creating a new database in some format through querying partial data from the existing table that MySQL can open (showing in the schema) before deleting the existing IBD file completely? And what is the possible reason that MySQL app cannot run after I changed the folder path in my.ini file? since the current drive has a very small storage, I will mostly likely change it to a different drive which is bigger to cater for future database growth.

I messed up a bit with a Dockerized PostgreSQL instance and accidentally overwrote the values I had for POSTGRES_USER and POSTGRES_PASSWORD. They were randomly generated, so guessing them is basically out of the question. By nature of Dockerized PostgreSQL, if you specify your own username, the role postgres will not be created. That means that postgres, admin, root are all invalid roles on my DB instance. Now I'm stuck with a data directory in a volume that I can access and a psql that I cannot access because I don't know any valid username on that database. Is there a list of usernames in this data directory that I can somehow access to get inside and change the password?

I was wondering if this is possible with mysql 5.6. I have a Database with several tables separated by Month (eg TonsOfRows_201411). I don't need access over the whole data so I decided to separate tables instead of partitioning. The current month is always used for importing/queries (we are talking some 100m rows per month) so I'd like to put the current table on a SSD and then archive it to a slower HDD afterwards (I still need to run queries on it, r/o only) In postgresql I create a tablespace with different physical location and can change the tablespace later on, so I can move around the table. I havent found a 'datadir' option in the mysql tablespaces, so not sure if I can solve it via mySQL tablespaces. I have found a 'datadir' for InnoDB, the problem is alter table doesn't support a change of the datadir (datadir is ignored for alter table), so not sure how to change it. So I was wondering if its possible to change the datadir of a mysql table after you have created it (with mySQL commands)? One solution would be symbolic links, but according to the Docu this is not supported for innoDB (nevertheless it is working, but i really prefer a SQL only solution here) Your help is appreciated, I am sure I am missing something here.

I have an AWS EC2 instance with RHEL 7 on which I installed mysql-5.5 on xvda. Its datadir is under /var/lib/mysql (as is the default). *I am importing dumps from a database on version 5.5.29 and the currently installed version is 5.5.60* A larger volume xvdf has been attached since I was running out of space and mounted it at /data_drive Then followed these steps to change the data directory: - Stop mysqld and verify if it had stopped - used rsync -av /var/lib/mysql /data_drive to move folder with permissions - moved the older mysql data directory to a backup file on the xvda (original volume) - Edited /etc/my.cnf to change the [mysqld] block to reflect the new location in both datadir and socket `datadir=/data_drive/mysql socket=/data_drive/mysql/mysql.sock` - added the client block at the end of the file `[client] port=3306 socket=/data_drive/mysql/mysql.sock` - saved the file and then tried to restart the mysqld service as root user and non-root user but it won't start. Here is the /var/log/mysqld.log output from the relevant timestamps: 180713 19:27:57 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 180713 20:20:37 mysqld_safe Logging to '/var/log/mysqld.log'. 180713 20:20:37 mysqld_safe Starting mysqld daemon with databases from /data_drive/mysql 180713 20:20:37 [Warning] Can't create test file /data_drive/mysql/ip-10-0-0-180.lower-test 180713 20:20:37 [Note] /usr/sbin/mysqld (mysqld 5.5.60) starting as process 18669 ... 180713 20:20:37 [Warning] Can't create test file /data_drive/mysql/ip-10-0-0-180.lower-test 180713 20:20:37 [Warning] Can't create test file /data_drive/mysql/ip-10-0-0-180.lower-test 180713 20:20:37 [Note] Plugin 'FEDERATED' is disabled. /usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13) 180713 20:20:37 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 180713 20:20:37 InnoDB: The InnoDB memory heap is disabled 180713 20:20:37 InnoDB: Mutexes and rw_locks use GCC atomic builtins 180713 20:20:37 InnoDB: Compressed tables use zlib 1.2.3 180713 20:20:37 InnoDB: Using Linux native AIO 180713 20:20:37 InnoDB: Initializing buffer pool, size = 128.0M 180713 20:20:37 InnoDB: Completed initialization of buffer pool 180713 20:20:37 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'open'. InnoDB: Cannot continue operation. When I ran sudo mysql_upgrade it gave the following output: Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Error: Failed while fetching Server version! Could be due to unauthorized access. FATAL ERROR: Upgrade failed This is how the /data_drive/mysql directory looks at the moment: [ec2-user@ip-10-0-0-180 mysql]$ ls -al /data_drive/mysql/ total 5921812 drwxr-xr-x. 4 mysql mysql 4096 Jul 13 18:45 . drwxr-xr-x. 5 ec2-user root 4096 Jul 13 18:46 .. -rw-rw----. 1 mysql mysql 6053429248 Jul 13 18:45 ibdata1 -rw-rw----. 1 mysql mysql 5242880 Jul 13 18:45 ib_logfile0 -rw-rw----. 1 mysql mysql 5242880 Jul 13 18:35 ib_logfile1 drwx------. 2 mysql root 4096 Jul 12 19:42 mysql drwx------. 2 mysql mysql 4096 Jul 12 19:42 performance_schema

I'm trying to move my MySQL datadir from /var/lib/mysql over to /home/mysql (an ssd drive as I'm having iowait issues on /var/lib/mysql when MySQL is running on that drive). The OS is CentOS 5.6 (final), MySQL version is 5.5.17-log and Selinux is disabled. ---- I've read many how-tos on the web and here is the procedure I'm using: 1. mkdir /home/mysql and then chown mysql:mysql /home/mysql

2. flush tables;

3. service mysqld stop

4. cp -R -p /var/lib/mysql/* /home/mysql/

5. mv /var/lib/mysql /var/lib/mysql_off

6. in my.cnf change datadir=/home/mysql and change socket=/home/mysql/mysql.sock

7. service mysqld start (it starts fine,OK)

---- When I go to load my website (or any other sites on this same server) I get this error: error sql: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' So it's clearly trying to access the old socket location which is invalid since I'm changing the MySQL directory to mysql_old. The mysql error log does not show any issues when i start up and does show the new socket location, with no errors (see bottom for the mysql error log) Solutions I've tried: adding to my.cnf: [client] socket=/home/mysql/mysql.sock and then starting mysqld -> same error. --- I know I can just make a sym link of /var/lib/mysql -> /home/mysql and that will solve this but I want to know why I'm getting that error even with the [client] socket=/home/mysql/mysql.sock and how to fix this so that I don't have to make a symlink. --- Other info: ls -la of actual /home/mysql: drwxr-xr-x 26 mysql mysql 4096 Aug 16 21:40 mysql ls -la of actual /var/lib/mysql: drwxr-xr-x 26 mysql mysql 4096 Aug 16 21:41 mysql [root@x home]# sestatus -v

SELinux status: disabled mysqld error log with socket and data dir set to my new locations: /home/mysql/: 140816 21:39:36 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 140816 21:39:55 mysqld_safe Starting mysqld daemon with databases from /home/mysql 140816 21:39:55 [Note] Plugin 'FEDERATED' is disabled. 140816 21:39:55 InnoDB: The InnoDB memory heap is disabled 140816 21:39:55 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140816 21:39:55 InnoDB: Compressed tables use zlib 1.2.3 140816 21:39:55 InnoDB: Using Linux native AIO 140816 21:39:55 InnoDB: Initializing buffer pool, size = 256.0M 140816 21:39:55 InnoDB: Completed initialization of buffer pool 140816 21:39:55 InnoDB: highest supported file format is Barracuda. 140816 21:39:55 InnoDB: Waiting for the background threads to start 140816 21:39:56 InnoDB: 1.1.8 started; log sequence number 784856558 140816 21:39:56 [Warning] 'user' entry 'root@xxx.xxxx.com' ignored in --skip-name-resolve mode. 140816 21:39:56 [Warning] 'user' entry '@xxxx.xxxx.com' ignored in --skip-name-resolve mode. 140816 21:39:56 [Note] Event Scheduler: Loaded 0 events 140816 21:39:56 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.17-log' socket: '/home/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) by Remi --- mysqld error log prior with socket and data dir set to: /var/lib/mysql: 140816 21:40:09 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 140816 21:40:59 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 140816 21:40:59 [Note] Plugin 'FEDERATED' is disabled. 140816 21:40:59 InnoDB: The InnoDB memory heap is disabled 140816 21:40:59 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140816 21:40:59 InnoDB: Compressed tables use zlib 1.2.3 140816 21:40:59 InnoDB: Using Linux native AIO 140816 21:40:59 InnoDB: Initializing buffer pool, size = 256.0M 140816 21:40:59 InnoDB: Completed initialization of buffer pool 140816 21:40:59 InnoDB: highest supported file format is Barracuda. 140816 21:40:59 InnoDB: Waiting for the background threads to start 140816 21:41:00 InnoDB: 1.1.8 started; log sequence number 784856558 140816 21:41:00 [Warning] 'user' entry 'root@xxxx.xxxx.com' ignored in --skip-name-resolve mode. 140816 21:41:00 [Warning] 'user' entry '@xxx.xxxx.com' ignored in --skip-name-resolve mode. 140816 21:41:00 [Note] Event Scheduler: Loaded 0 events 140816 21:41:00 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.17-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) by Remi

I'm using the following query to view files/folders in a given directory. It works locally, but when trying to view a mapped drive I get... "The system cannot find the path specified." I have the permissions for the whole mapped drive set to the service account associated with SQL, but it is still not working. declare @files table (ID int IDENTITY, FileName varchar(100)) insert into @files execute xp_cmdshell 'dir Z:\ /b' select * from @files End goal is to create a stored procedure that will transact specific file names into a table.

How do I change the MySQL data folder path in a Mac? - I am running OS X 10.10 & MySQL 5.6 - In Windows I can edit datadir in my.ini - For Mac, mysqld does not have parameter for --defaults-file=

We ran ran into a problem where MySQL kept crashing because of corruption in one of our databases. MySQL would start, crash, and then restart. We determined that the corruption was in a database containing archive tables (i.e. tables using the archive engine). We weren't able to drop the database in MySQL because it wouldn't stay up long enough to drop it, so we shut down MySQL and moved the directory containing the database to another location. This prevented MySQL from crashing, and all of our other databases seem to be working fine. My question is: is moving the database directory containing the database out of MySQL's data directory a safe way to remove a database? Is it the same as dropping a database?

I moved mysql datadir to /storage-s01/mysql, but socket is still located at the same directory /var/lib/mysql/mysql.sock. Do I have to keep socket at the same directory as datadir? BTW, If I want to change datadir, i have to change that only /etc/my.cnf file? I have mariadb

Some background: There are 3 database servers - two in a master/master replication config with only one server accepting writes. And one other slave replica replicating from the write-master. The slave-master had low disk space recently (to the point where it ran entirely out of space), so I added an additional disk and moved only the datadir to the new disk. Prior to doing this, the slave master was accepting writes. But I moved the writes over to the other master before moving data. The strange thing is that the datadir of the slave-master is approximately 175GB larger than the write-master. That is until I run the query: select table_schema,table_name,data_length from tables order by data_length desc limit 10; As this query is running the OS reports that the amount of free space is increasing. And ends up around 120GB extra. This happens in the space of a minute. Then immediately after the query finishes the free disk space begins to decrease again. This is with MySQL v5.6. The majority of tables are MyISAM with a minority of InnoDB tables. A show slave status on the slave-master reports replication continues as normal. The logs dir, which remains on the original disk, does not fluctuate free space wildly like this. Has any seen this behaviour before? This seems somewhat sinister to me, but I'm by no means an expert. Running the same query on the current write-master doesn't produce this behaviour.

My problem yesterday (https://dba.stackexchange.com/questions/91483/mysql-alter-table-fails-as-workbench-tries-to-create-existing-table) was caused by /dev/sda2 (/root) running out of space. The cause of this was that, by default, MySQL had installed the DataDirectory (datadir) on /dev/sda2(/root). I therefore recursively copied its contents to /dev/sda3 (/home) after stopping the MySQL server. I also editted /etc/init.d/mysql, /etc/mysql/my.cnf and /etc/apparmor.d/usr.bin.mysqld to reflect this change. After both trying to restart the MySQL server and a reboot the server is still not running. The error.log in /var/log/mysql is also not being appended to. Obviously I have missed something but what? I am running Linux Mint 13.