Tag Archives: MySQL

mysql export users and passwords to another server

mysqldump migrate users to new server


MySQL 5.6 and older

while read line; do mysql -B -N -e "SHOW GRANTS FOR $line"; done < <(mysql -B -N -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user != 'debian-sys-maint' AND user != 'root' AND user != ''" mysql) | sed 's/$/;/' > mysql_all_users_grants.sql

if MySQL 5.7 and above

while read line; do mysql -B -N -e "SHOW CREATE USER $line"; done < <(mysql -B -N -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user != 'mysql' AND user != 'root' AND user != ''" mysql) | sed 's/$/;/' > mysql_create_users.sql

while read line; do mysql -B -N -e "SHOW GRANTS FOR $line"; done < <(mysql -B -N -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user != 'mysql' AND user != 'root' AND user != ''" mysql) | sed 's/$/;/' > mysql_all_users_grants.sql

Excludes some users like root

mysqld[]: mysqld: Can’t read dir of ‘/etc/mysql/mysql.conf.d/’ (Errcode: 13 – Permission denied)

journalctl -u mysql.service -f

mysqld[]: mysqld: Can’t read dir of ‘/etc/mysql/mysql.conf.d/’ (Errcode: 13 – Permission denied)
mysqld[]: mysqld: [ERROR] Fatal error in defaults handling. Program aborted!
mysql.service: Control process exited, code=exited, status=1/FAILURE
systemd[1]: mysql.service: Failed with result ‘exit-code’.
systemd[1]: Failed to start MySQL Cluster Community Server.

ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/
apparmor_parser -R /etc/apparmor.d/usr.sbin.mysqld

mysql slave read only


Make sure you are working on slave server:

SHOW SLAVE STATUS \G ;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

SELECT @@global.read_only, @@global.super_read_only;
+--------------------+--------------------------+
| @@global.read_only | @@global.super_read_only |
+--------------------+--------------------------+
| 0 | 0 |
+--------------------+--------------------------+
1 row in set (0.00 sec)

SET GLOBAL super_read_only = 1;

SELECT @@global.read_only, @@global.super_read_only;
+--------------------+--------------------------+
| @@global.read_only | @@global.super_read_only |
+--------------------+--------------------------+
| 1 | 1 |
+--------------------+--------------------------+
1 row in set (0.00 sec)

Save config settings:
super_read_only = On

mysql> INSERT INTO authors (id,name,email) VALUES(1,"Test","[email protected]");
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

If ERROR 1193 (HY000): Unknown system variable 'super_read_only'
SET GLOBAL read_only = 1;
UPDATE mysql.user SET super_priv='N' WHERE user<>'root';
FLUSH PRIVILEGES;

select user,host,plugin,Super_priv from mysql.user;
+------------------+-----------+-------------+------------+
| user | host | plugin | Super_priv |
+------------------+-----------+-------------+------------+
| root | localhost | unix_socket | Y |
| replica_user | % | | N |

list slave mysql

show slave mysql

mysql> SELECT * FROM information_schema.PROCESSLIST AS p WHERE p.COMMAND = 'Binlog Dump';

+---------+------+-------------------------+------+-------------+---------+---------------------------------------------------------------+------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+---------+------+-------------------------+------+-------------+---------+---------------------------------------------------------------+------+
| 3973053 | user | srv-mysql-slave1:53014 | NULL | Binlog Dump | 3743978 | Master has sent all binlog to slave; waiting for more updates | NULL |
+---------+------+-------------------------+------+-------------+---------+---------------------------------------------------------------+------+

ERROR 1698 (28000): Access denied for user ‘root’@’localhost’

MariaDB [mysql]> SELECT User, Host, plugin FROM mysql.user;
+------+-----------+-------------+
| User | Host | plugin |
+------+-----------+-------------+
| root | localhost | unix_socket |

mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;

MariaDB [mysql]> SELECT User, Host, plugin FROM mysql.user;
+------+-----------+-----------------------+
| User | Host | plugin |
+------+-----------+-----------------------+
| root | localhost | mysql_native_password |
+------+-----------+-----------------------+

service mysql restart