| ../ |
|---|
I have MySQL 8.0 installed on CentOS 8.3. I want to stop distinguishing between the case of table names and column names as in the Windows version. To do this, set my.cnf to lower_case_table_names = 1 and restart mysqld. That was fine before. However, for some time unknown, the procedure in MySQL 8.0 has not been easy.
Lower_case_table_names can only be specified immediately after the initial installation of MySQL 8.0. To return to the initial installation state, you need to delete the datastore and recreate the datastore. Make a note of the procedure.
-(1) Take a dump of the existing DB -(2) Delete the data store in secret -(3) Set lower_case_table_names = 1 and recreate the datastore -(4) Set the root password -(5) Set GRANT by create database and create user -(6) Restore from dump
Make a backup of the existing user-defined DB. Save all DB except mysql database. In the example below, it is a kankeri database and a xxx database.
$ mkdir -p /var/tmp/mysql
$ mysqldump -uroot -p kankeri > /var/tmp/dump-kankeri.sql
$ mysqldump -uroot -p xxx > /var/tmp/dump-xxx.sql
Gently delete the MySQL datastore (/ var/lib/mysql). It is safe to mv and save. You should also clear mysqld.log. Easy to check for errors later.
$ mv /var/lib/mysql /var/tmp/mysql
$ rm -f /var/log/mysql/mysqld.log
$ touch /var/log/mysql/mysqld.log
$ chown mysql.mysql /var/log/mysql/mysqld.log
First, write lower_case_table_names = 1 in mysql-server.cnf. Add to cnf in the initial state. If extra settings remain, it will be difficult to isolate when an error occurs.
$ vi /etc/my.cnf.d/mysql-server.cnf
[mysqld]
lower_case_table_names=1
Then recreate the datastore and start the service. At this point, make sure that you can start without error.
$ mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql --console
$ ls -l /var/lib/mysql
$ more /var/log/mysql/mysqld.log
$ systemctl start mysqld
Add skip-grant-tables to mysql-server.cnf to set the root password and restart the service. If necessary, set the encoding as well. Enter the MySQL console with a root account without password authentication.
$ vi /etc/my.cnf.d/mysql-server.cnf
[mysqld]
character-set-server=utf8
lower_case_table_names=1
skip-grant-tables
$ vi /etc/my.cnf.d/client.cnf
[client]
default-character-set=utf8
$ systemctl restart mysqld
$ mysql -uroot
Let's check the user table. Also, check the password strength setting.
> use mysql
> select user,host,plugin,authentication_string from user;
+------------------+-----------+-----------------------+-----+
| user | host | plugin | authentication_string |
+------------------+-----------+-----------------------+-----+
| mysql.infoschema | localhost | caching_sha2_password | *** |
| mysql.session | localhost | caching_sha2_password | *** |
| mysql.sys | localhost | caching_sha2_password | *** |
| root | localhost | mysql_native_password | *** |
+------------------+-----------+-----------------------+-----+
> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 8 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | MEDIUM |
| validate_password.special_char_count | 1 |
+--------------------------------------+--------+
I weaken the password strength as follows. In the initial state, it is a mixture of uppercase letters, lowercase letters, numbers, and symbols. After that, set the password for the root account. A plain password that does not match the strength of the password will result in "ERROR 1819 (HY000): Your password does not satisfy the current policy requirements".
> set global validate_password.length=6;
> set global validate_password.policy=LOW;
> set global validate_password.special_char_count=0;
> flush privileges;
> alter user 'root'@'localhost' identified by '***'; #With any password
> flush privileges;
> exit;
Remove skip-grant-tables, reboot and make sure you can log in with the root account.
$ vi /etc/my.cnf.d/mysql-server.cnf
[mysqld]
character-set-server=utf8
lower_case_table_names=1
#skip-grant-tables #Comment out
$ systemctl restart mysqld
$ mysql -uroot -p mysql
Enter password: ***
Create the required user-defined database and account and set GRANT. In the example below, the kankeri and xxx databases. For the kankeri database, we have GRANTed accounts kadmin and kuser. Password strength is the same as root described above.
> use mysql;
> create database kankeri;
> create database xxx;
> CREATE USER kadmin@localhost IDENTIFIED BY '***';
> CREATE USER kuser@localhost IDENTIFIED BY '***';
> GRANT ALL PRIVILEGES ON kankeri.* TO kamin@localhost;
> GRANT DELETE,INSERT,SELECT,UPDATE ON kankeri.* TO kuser@localhost;
> flush privileges;
> select user,host,plugin,authentication_string from user;
+------------------+-----------+-----------------------+-----+
| user | host | plugin | authentication_string |
+------------------+-----------+-----------------------+-----+
| kadmin | localhost | mysql_native_password | *** |
| kuser | localhost | mysql_native_password | *** |
| mysql.infoschema | localhost | caching_sha2_password | *** |
| mysql.session | localhost | caching_sha2_password | *** |
| mysql.sys | localhost | caching_sha2_password | *** |
| root | localhost | mysql_native_password | *** |
+------------------+-----------+-----------------------+-----+
Restore the files backed up in the first step (1) and restore the data.
$ mysql -uroot -p kankeri < /var/tmp/dump-kankeri.sql
$ mysql -uroot -p xxx < /var/tmp/dump-xxx.sql
$ mysql -ukuser -p kankeri
Enter password: ***
> show tables;
that's all
| ../ |
|---|
Recommended Posts