I’m creating a mysql database via the CLI when I run this
command line
Create user meriemPFE;
he made me get this error message, which I could not resolve.
the server is running with the –skip-grant-tables option can not execute this statement.
EDIT:
yes I put it in the file C: xampp mysql bin my.ini
this is a portion of code
[mysqld]
skip-grant-tables
port= 3306
socket = "C:/xampp/mysql/mysql.sock"
tmpdir = "C:/xampp/tmp"
datadir = "C:/xampp/mysql/data"
pid_file = "mysql.pid"
8
As of recent versions of MySQL/MariaDB starting with --skip-grant-tables doesn’t work to reset the mysql root user password.
As per MySQL docs you now need a different process to reset the mysql root user.
Assuming a Linux machine running MariaDB as root (if not get root with sudo su root).
Then create a file somewhere that the mysql user can read. I use /var/lib/mysql/mysql-init (but it can be anywhere really). In that file put the following:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
(Obviously change MyNewPass to be your desired password).
As noted the mysql user must have read access to this file. To ensure no other users can read it I recommend chowning it and set permissions to read only for all other users. I.e.:
chown mysql:mysql /var/lib/mysql/mysql-init
chmod 0400 /var/lib/mysql/mysql-init
Then stop MariaDB, assuming systemd:
systemctl stop mariadb
Then start it again from CLI:
mysqld --init-file=/var/lib/mysql/mysql-init &
(note other options may be required, but just like that works for me)
Once it has successfully started, then kill it again:
kill $(pgrep mysqld)
And restart the service:
systemctl start mariadb
And you should be good to go with your new password!
I also suggest cleaning up the mysql-init file:
rm /var/lib/mysql/mysql-init
If like me you want to change to use the unix_socket then:
mysql -e "ALTER USER root@localhost IDENTIFIED VIA 'unix_socket';"
In mysql, verify executing the flush privileges command.
mysql> flush privileges
This action solved the error in my case.
the server is running with the –skip-grant-tables option can not
execute this statement.
I hope this helps you too. Health!