SET GLOBAL sql_mode = '';
Incorrect date value: ‘0000-00-00’ for column ” at row 1
Leave a reply
Tag Archives: MySQL
set expire_logs_days
mysql> SET GLOBAL expire_logs_days=7;
mysql uptime days
SELECT
VARIABLE_VALUE AS Uptime_seconds,
NOW() AS "Now",
NOW() - INTERVAL VARIABLE_VALUE SECOND AS "Up since",
DATEDIFF(NOW(), NOW() - INTERVAL VARIABLE_VALUE SECOND) AS "Uptime_days"
FROM performance_schema.session_status
WHERE VARIABLE_NAME = 'Uptime';
mysql change root auth_socket
UPDATE mysql.user SET plugin = 'mysql_native_password', authentication_string = PASSWORD('changeme') WHERE User = 'root';
FLUSH PRIVILEGES
mysql show loaded config file
which mysqld
/usr/sbin/mysqld
/usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘– INTERVAL 1 DAY’ at line 1
PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 1 DAY);
or exact day:
PURGE BINARY LOGS BEFORE '2021-04-11 00:00:00';
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
mysql drop all users except root
while read line; do mysql -B -N -e "drop user $line"; done < <(mysql -B -N -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user != 'debian-sys-maint' AND user != 'root' AND user != ''" mysql)
mariadb 10.4 ubuntu 20.04
apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://nyc2.mirrors.digitalocean.com/mariadb/repo/10.4/ubuntu focal main'
apt update
apt install mariadb-server
journalctl mysql logs
journalctl -u mysql.service -f
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 |
mysqldump to remote server ssh
mysqldump --single-transaction sourcedb | ssh remote_server_ip 'mysql destinationdb'
ALTER tables without lock
pt-online-schema-change alters a table’s structure without blocking reads or writes. Specify the database and table in the DSN. Do not use this tool before reading its documentation and checking your backups carefully.
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 |
+---------+------+-------------------------+------+-------------+---------+---------------------------------------------------------------+------+