Cleanly reinstall mysql 8

chwba

Verified User
Joined
Dec 23, 2019
Messages
40
Hello,
So I have posted this on StackOverflow but I doubt I will get any answer. I have tried pretty much everything but since I upgraded from mysql 5 to mysql 8 (and migrated the existing databases) I can only run mysql with the
Code:
--skip-grant-tables
argument which is not a long term solution at all.

So I wanted to ask how I can cleanly reinstall mysql 8 and convert all databases to the mysql 8 syntax and to
Code:
utf8mb4_unicode_ci
as some of them are
Code:
utf8_general_ci

Any help is greatly appreciated because I really can't lose these databases, else my whole WordPress website will be killed.
 
What exactly do you mean by backups? The database itself I do have it and use it on the newer sql version but as explained can only use it with skip-grant-tables option. I had seen that site, yes but are you sure I can use this guide for what I am planning to do?
 
If you had backups from prior versions, you may have been able to restore and re-upgrade from the old version to the new version.

The page I linked was updated within the last 4 months. It will remove your SQL data so please back up your databases and store copies else where.
Code:
cd /usr/local/directadmin/custombuild
./build set mysql_backup yes
./build mysql_backup

Located at /usr/local/directadmin/custombuild/mysql_backups

A typo on that page would be "./build rouncdube" which should actually be "./build roundcube".

Otherwise I see no reason for it not to work. It moves the sql data creating a blank directory for custombuild to re-install MySQL. (https://help.directadmin.com/item.php?id=240)

From https://help.directadmin.com/item.php?id=678 to restore all user databases:
Code:
cd /usr/local/directadmin/custombuild/mysql_backups
wget http://files1.directadmin.com/services/all/mysql/restore_sql_files.sh
chmod 755 restore_sql_files.sh
./restore_sql_files.sh
 
Thank you for this very nice writeup, the reinstall worked but sadly the "mysql" and the "sys" databases are also being restored and these ones seems to contain the misconfiguration, will it work if I dont restore this one?
 
The remove/reinstall sql help page says "usually avoid doing this unless you've lost all your mysql user/passwords" about restoring "mysql".

If you've got all the files to recover from any potential errors then personally I would test without restoring those.
 
Yeah I tried to not restore these two now and now I cant connect to the da database through php myadmin but through shell i can access it so the password is correct, really weird..
 
Managed to login to phpmyadmin with root user, then just had to add the user back for the wordpress db and it works. Also did some tweaking of the my.cnf, maybe you got any comments on it?

Code:
[client]
default-character-set = utf8mb4
socket          = /var/lib/mysql/mysql.sock

[mysql]
default-character-set = utf8mb4

[mysqld]
innodb_file_per_table=1
skip-name-resolve
character-set-client-handshake = FALSE
collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4
default_authentication_plugin = mysql_native_password
max_user_connections=800
max_connections=1000
interactive_timeout=10
wait_timeout=20
connect_timeout=20
sql-mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
innodb_thread_concurrency=0
innodb_log_file_size=16m
join_buffer_size  = 2M
slow_query_log = 1
slow_query_log_file = /var/log/mysql-slow-queries.log
long_query_time = 10
binlog_cache_size = 16M
innodb_buffer_pool_instances=11


[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
safe-updates
 
Back
Top