Fundamental Usage of MySQL

This blog is just to solve some bugs while using MySQL as a server. Assuming you've already installed mysql-server, mysql-server-5.7, and mysql-server-core-5.7.

1. Errors

1.1 Failed to start mysql.service: Unit mysql.service not found.

➜  ~ sudo service mysqld start
[sudo] password for jiapei:
Failed to start mysqld.service: Unit mysqld.service not found.
➜  ~ /etc/init.d/mysql stop
[ ok ] Stopping mysql (via systemctl): mysql.service.
➜  ~ /etc/init.d/mysql start
[ ok ] Starting mysql (via systemctl): mysql.service.

Conclusion: use /etc/init.d/mysql start instead of service mysqld start.

1.2 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

➜  ~ mysql -u root -p
Enter password: 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

We FIRST list all existing MySQL processes and kill them all.

➜  ~ ps aux | grep mysql
...
➜  ~ sudo kill PID

Util now, there should be NO MySQL process running.

1.3 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

Since there is NO MySQL process running, of course we cannot connect to MySQL server.

➜  ~ mysql -u root -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
➜  ~ /etc/init.d/mysql start
[....] Starting mysql (via systemctl): mysql.serviceJob for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.
 failed!

Then, we will have to have these 3 packages reinstalled: mysql-server, mysql-server-5.7, and mysql-server-core-5.7.

➜  ~ sudo apt remove mysql-server mysql-server-5.7 mysql-server-core-5.7
...
➜  ~ sudo apt install mysql-server mysql-server-5.7 mysql-server-core-5.7
...

/var/run/mysqld/mysqld.sock is NOW back, and MySQL seems to run automatically.

➜  ~ ls -ls /var/run/mysqld/mysqld.sock
0 srwxrwxrwx 1 mysql mysql 0 Nov 30 03:08 /var/run/mysqld/mysqld.sock
➜  ~ ps aux | grep mysql
mysql    13785  0.1  0.3 1323300 172680 ?      Sl   03:08   0:00 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
jiapei   14453  0.0  0.0  21556  2560 pts/0    R+   03:16   0:00 grep --color=auto --exclude-dir=.bzr --exclude-dir=CVS --exclude-dir=.git --exclude-dir=.hg --exclude-dir=.svn mysql

Then, use chown and chmod to set up the owners and permissions suitably as follows.

➜  run pwd
/var/run
➜  run sudo chown mysql:mysql -R mysql
➜  run sudo chmod 755 -R mysql
➜  run ls -lsd mysql
0 drwxr-xr-x 2 mysql mysql 100 Nov 30 02:38 mysqld
➜  run cd ../lib
➜  lib pwd
/var/lib
➜  lib sudo chown mysql:mysql -R mysql
➜  lib sudo chmod 755 -R mysql
➜  lib ls -lsd mysql
4 drwxr-xr-x 6 mysql mysql 4096 Nov 30 02:38 mysql

1.4 ERROR 1698 (28000): Access denied for user 'root'@'localhost'

➜  ~ mysql -u root -p
Enter password: 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
➜  ~ sudo mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.24-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> USE lvrsql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE USER 'YOUR_SYSTEM_USER'@'localhost' IDENTIFIED BY 'YOUR_PASSWORD';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'YOUR_SYSTEM_USER'@'localhost';
mysql> FLUSH PRIVILEGES;
mysql> exit;

➜  ~ service mysql restart
➜  ~ 

1.5 [ERROR] InnoDB: Unable to lock ./ibdata1 (Additional)

If you meet the above ERROR message, what you can do is to restart MySQL by:

➜  ~ /etc/init.d/mysql restart
[ ok ] Restarting mysql (via systemctl): mysql.service.
➜  ~ 

2. Fundamental Commands in MySQL