UPDATE mysql.db SET Host='%' WHERE Host='localhost' AND User='username';
FLUSH PRIVILEGES;
Tag Archives: MySQL
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
mysql create user
mysql create user
CREATE USER 'db_user'@'%' IDENTIFIED BY 'db_password';
GRANT ALL PRIVILEGES ON db_name.* TO 'db_user'@'%';
OR:
grant all on db_name.* to 'db_user'@'localhost' identified by 'db_password';
mysqldump: Error 1412: Table definition has changed, please retry transaction when dumping table
Try to dump without single-transaction option with mysqldump
percona upgrade 5.7 to 8.0
percona-release enable ps-80 release
apt-get update
apt-get install percona-server-server
mysql_upgrade
service mysql start
mysql -V
mysql Ver 8.0.21-12 for Linux on x86_64 (Percona Server (GPL), Release ’12’, Revision ‘7ddfdfe’)
ubuntu mysql 8
wget -c https://repo.mysql.com//mysql-apt-config_0.8.13-1_all.deb
dpkg -i mysql-apt-config_0.8.13-1_all.deb
apt update
apt install mysql-server
mysql.ping mysqladmin: connect to server at ‘localhost’ failed
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'zabbix'@'localhost' (using password: NO)'
0
Fix:
UserParameter=mysql.ping,mysqladmin --defaults-extra-file='/etc/zabbix/.my.cnf' ping | grep -c alive
mysql client cannot find socket
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
mysqld --verbose --help | grep ^socket
socket /var/lib/mysql/mysql.sock
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 ‘identified by
CREATE USER 'root'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
compare 2 database tables
sdiff <(mysql -B -e "show tables;" -D a_database) <(mysql -B -e "show tables;" -D b_database)
skip same record from first col:
sdiff <(mysql -B -e "show tables;" -D a_database) <(mysql -B -e "show tables;" -D b_database) | grep '[<>]'
mysql disallow remote login
bind-address=127.0.0.1
mysql delete old binlog data
PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY)
PURGE BINARY LOGS BEFORE DATE(NOW()) + INTERVAL 0 SECOND - INTERVAL 3 DAY;
mysql binlog limit file number
max_binlog_files = 10
mysql binlog how much days
show global variables like "%expire_logs_days%";
show global variables like "%binlog_expire_logs_seconds%";
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
If Last_IO_Errno: 0, so its fine if error like 2049 it could be password related issues