STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
SHOW SLAVE STATUS \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
try fix MySQL slave
Leave a reply
Tag Archives: MySQL
Incorrect date value: ‘0000-00-00’ for column ” at row 1
SET GLOBAL sql_mode = '';
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.