- Hayato Ishida·Last edited by Hayato Ishida on Aug 9, 2024Linked with GitHubContributed by
MySQL on WSL
Image Not Showing
Possible Reasons
Learn More →
Image Not Showing
Possible Reasons
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
1. Environment
Ubuntu 20.04.5 LTS
2. Install MySQL
2-1. Install MySQL Client
$ sudo apt install mysql-client
2-2. Install MySQL Server
$ sudo apt install mysql-server
2-3. Check Version
Check if MySQL is installed.
$ mysql --version
mysql Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using EditLine wrapper
2-4. Check Behaviour
2-4-1. Start
$ sudo /etc/init.d/mysql start
2-4-2. Stop
$ sudo /etc/init.d/mysql stop
2-4-3. Restart
$ sudo /etc/init.d/mysql restart
3. Reset Root Password
3-1. Prepare for Connecting to MySQL without Authentication
$ sudo mysqld_safe --skip-grant-tables &
[1] 5676
* --skip-grant-tables & allows anyone to log in MySQL anywhere and anytime.
It is highly dangerous, so it should be used only in the initial settings.
Please refer to Official Document for the details.
3-2. Connecting to MySQL without Password as Root User
Log in as the root user with -u.
$ sudo mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.27-0ubuntu0.18.04.1 (Ubuntu)
Copyright (c) 2000, 2019, 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>
3-3. Password Setting
mysql> UPDATE mysql.user SET authentication_string = "", password_expired = "N" WHERE User = "root";
mysql> UPDATE mysql.user SET plugin="mysql_native_password" WHERE User="root";
mysql> FLUSH PRIVILEGES;
exit
Please see the explanation of each SQL shown below.
3-3-1. UPDATE mysql.user SET authentication_string = "", password_expired = "N" WHERE User = "root";
DB_NAME.TABLE_NAME finds the DB and table to update.
Set the password in the authentication_string column and N in password_expired where the user is root.
3-3-2. UPDATE mysql.user SET plugin = "mysql_native_password" WHERE User = "root;
Set mysql_native_password in authentication_string column for authentication plugin in the account where the user is root.
3-3-3. FLUSH PRIVILEGES;
Reload on Grant Tables to reflect all changes.
4. Connect to MySQL
4-1. Prepare for Connecting to MySQL with Authentication
$ sudo mysqld_safe
4-2. Connect to MySQL with Password
Log in as the root user with the password using -u and p.
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.27-0ubuntu0.18.04.1 (Ubuntu)
Copyright (c) 2000, 2019, 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>
Now MySQL setup is done.
5. Notes
Refer to the user table in mysql database with the SQL below.
5-1. Show DB
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
5-2. Go into DB
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
5-3. Show Tables
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.01 sec)
5-4. Get Record from Table
mysql> select * from user;
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | auth_socket | | N | 2019-11-08 00:00:00 | NULL | N |
| localhost | mysql.session | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *HOGEHOGEHOGEHOGEHOGEHOGEHOGEHOGEHOGEHOGE | N | 2019-11-08 00:00:00 | NULL | Y |
| localhost | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *HOGEHOGEHOGEHOGEHOGEHOGEHOGEHOGEHOGEHOGE | N | 2019-11-08 00:00:00 | NULL | Y |
| localhost | debian-sys-maint | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *HOGEHOGEHOGEHOGEHOGEHOGEHOGEHOGEHOGEHOGE | N | 2019-11-08 00:00:00 | NULL | N |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
4 rows in set (0.00 sec)
* w3schools.com explains SQL usages in detail.
Read more
Bothered by Polymorphic Relations
hackmd-github-sync-badge
Aug 9, 2024Basics Of Technical Documentation For Engineers -解釈と分析-
Basics Of Technical Documentation For Engineers, Ayca LITTLE, 25 April 2018, Published inDatasheetEST by TDSmaker
Aug 9, 2024Ransack Search Form
hackmd-github-sync-badge
Aug 9, 2024Sort without Ransack
hackmd-github-sync-badge
Aug 9, 2024Published on 
HackMD
HackMD