Linux Ubuntu Changing the datadir (data directory) of mysql

 

mysqld.cnf

datadir = /new/data/dir

mysqld --user=mysql --initialize

innodb_flush_log_at_trx_commit= 0
ssl=0 #  (5.7 community (self built) might not use SSL for cases. 8.0 always use ssl by default, might be slower)
innodb_doublewrite_pages=120 # (if doublewrite enabled for recent 8.0.x "on HDD")
innodb_undo_log_truncate=OFF
innodb_log_writer_threads=OFF
 

plugin-load-add=auth_socket.so
auth_socket=FORCE_PLUS_PERMANENT

 

sudo systemctl stop apparmor

sudo systemctl disable apparmor

sudo mysqldump --master-data=1 --flush-logs --single-transaction --routines --quick --all-databases > db20230116_1852.sql

CREATE USER 'XXXXX'@'%' IDENTIFIED WITH mysql_native_password BY 'XXXXXX';
GRANT ALL PRIVILEGES ON *.* TO 'XXXXX'@'%' WITH GRANT OPTION;
FLUSH PRIVILIGES;