MySQL

ERROR 1045 (28000): Access denied for user 'xxx'@'xxx' (using password: YES)

: MySQL

:, host

Solutions:

1. : -p,
2. : SELECT user, host FROM mysql.user WHERE user = 'xxx';
3. host : 'user'@'localhost' and 'user'@'%'
4. : ALTER USER 'user'@'host' IDENTIFIED BY 'newpassword';
5. root : --skip-grant-tables MySQL

: ~/.my.cnf ； host

ERROR 2003 (HY000): Can't connect to MySQL server on 'host' (111) / ERROR 2002: Can't connect through socket

: MySQL

:MySQL,, bind-address

Solutions:

1. MySQL : systemctl status mysql
2. MySQL : systemctl start mysql
3. : ss -tlnp | grep 3306
4. bind-address : grep bind-address /etc/mysql/mysql.conf.d/mysqld.cnf( 0.0.0.0 )
5. : ufw allow 3306 firewall-cmd --add-port=3306/tcp --permanent
6. Socket socket : mysql -S /var/run/mysqld/mysqld.sock -u root -p

: MySQL : systemctl enable mysql； MySQL

ERROR 1146 (42S02): Table 'database.table_name' doesn't exist

: SELECT, INSERT, UPDATE and more

:,,

Solutions:

1. : SELECT DATABASE(); SHOW TABLES;
2. : SHOW TABLES LIKE '%order%';
3. : USE ecommerce;
4. : Linux MySQL, lower_case_table_names
5. :, binlog

: SQL database.table ；

ERROR 1062 (23000): Duplicate entry 'xxx' for key 'PRIMARY' / 'uk_xxx'

: INSERT UPDATE

:New

Solutions:

1. : SELECT * FROM table WHERE unique_column = 'xxx';
2. INSERT.. ON DUPLICATE KEY UPDATE New: INSERT INTO t (id, name) VALUES (1, 'new') ON DUPLICATE KEY UPDATE name = 'new';
3. INSERT IGNORE ()
4. REPLACE INTO (, ID)
5. ,

: SELECT ； and more； ON DUPLICATE KEY UPDATE

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

: UPDATE, DELETE SELECT.. FOR UPDATE and more

:(, )

Solutions:

1. and more: SELECT * FROM information_schema.INNODB_LOCK_WAITS;(MySQL 5.7) SELECT * FROM performance_schema.data_lock_waits;(MySQL 8.0)
2. : SHOW ENGINE INNODB STATUS; LATEST DETECTED DEADLOCK
3. : SHOW PROCESSLIST; KILL <blocking_thread_id>;
4. : SELECT * FROM information_schema.INNODB_TRX WHERE trx_state = 'RUNNING';
5. and more(): SET innodb_lock_wait_timeout = 120;

:, ；New； SQL