PCTResearch Wiki

User Tools

Site Tools

Trace:
mysql

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		Previous revision
mysql [2025/02/21 19:15] – created jianwumysql [2025/02/26 03:55] (current) – jianwu
Line 1: Line 1:
 +[[https://pctresearch.com/|{{:wiki_banner.jpg?nolink&800|}}]]
 +
 ===== MySQL ===== ===== MySQL =====
  [[https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-22-04|How To Install MySQL on Ubuntu 22.04]]  [[https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-22-04|How To Install MySQL on Ubuntu 22.04]]
Line 5: Line 7:
 $ sudo apt install mysql-server $ sudo apt install mysql-server
 $ sudo systemctl status mysql $ sudo systemctl status mysql
-[sudo] password for kan:+[sudo] password for user_name:
 ● mysql.service - MySQL Community Server ● mysql.service - MySQL Community Server
      Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; preset: enabled)      Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; preset: enabled)
Line 17: Line 19:
              └─77669 /usr/sbin/mysqld              └─77669 /usr/sbin/mysqld
  
- 2月 20 09:14:28 ubuntukan systemd[1]: Starting mysql.service - MySQL Community Server... + 2月 20 09:14:28 localhost systemd[1]: Starting mysql.service - MySQL Community Server... 
- 2月 20 09:14:29 ubuntukan systemd[1]: Started mysql.service - MySQL Community Server.+ 2月 20 09:14:29 localhost systemd[1]: Started mysql.service - MySQL Community Server.
 </code> </code>
 <code Bash> <code Bash>
Line 31: Line 33:
 <code Bash> <code Bash>
 $ sudo journalctl -u mysql $ sudo journalctl -u mysql
- 2月 20 09:14:28 ubuntukan systemd[1]: Starting mysql.service - MySQL Community Server... + 2月 20 09:14:28 localhost systemd[1]: Starting mysql.service - MySQL Community Server... 
- 2月 20 09:14:29 ubuntukan systemd[1]: Started mysql.service - MySQL Community Server.+ 2月 20 09:14:29 localhost systemd[1]: Started mysql.service - MySQL Community Server.
 </code> </code>
 https://ubuntu.com/server/docs/install-and-configure-a-mysql-server#database-engines https://ubuntu.com/server/docs/install-and-configure-a-mysql-server#database-engines
Line 135: Line 137:
 ===== Creating a Dedicated MySQL User and Granting Privileges ===== ===== Creating a Dedicated MySQL User and Granting Privileges =====
 <code MySQL> <code MySQL>
-mysql> create user 'username'@'host' identified with authentication_plugin by 'password';+mysql> create user 'user_name'@'host' identified with authentication_plugin by 'password';
 </code> </code>
-:!: Wrapping both the username and host in single quotes isn’t always necessary, but doing so can help to prevent errors.+:!: Wrapping both the user_name and host in single quotes isn’t always necessary, but doing so can help to prevent errors.
  
 create a user that authenticates with caching_sha2_password: create a user that authenticates with caching_sha2_password:
 <code MySQL> <code MySQL>
-mysql> create user 'sammy'@'localhost' identified by 'password';+mysql> create user 'user_name'@'localhost' identified by 'password';
 </code> </code>
 There is a known issue with some versions of PHP that causes problems with caching_sha2_password There is a known issue with some versions of PHP that causes problems with caching_sha2_password
Line 147: Line 149:
 If you plan to use this database with a PHP application — phpMyAdmin, for example — you may want to create a user that will authenticate with the older, though still secure, mysql_native_password plugin instead: If you plan to use this database with a PHP application — phpMyAdmin, for example — you may want to create a user that will authenticate with the older, though still secure, mysql_native_password plugin instead:
 <code MySQL> <code MySQL>
-mysql> create user 'sammy'@'localhost' identified with mysql_native_password by 'password';+mysql> create user 'user_name'@'localhost' identified with mysql_native_password by 'password';
 </code> </code>
 You can ''**alter**'' it later on with this command: You can ''**alter**'' it later on with this command:
 <code MySQL> <code MySQL>
-mysql> alter user 'sammy'@'localhost' identified with mysql_native_password by 'password';+mysql> alter user 'user_name'@'localhost' identified with mysql_native_password by 'password';
 </code> </code>
 The general syntax for granting user privileges is as follows: The general syntax for granting user privileges is as follows:
 <code MySQL> <code MySQL>
-mysql> grant privilege on database.table to 'username'@'host';+mysql> grant privilege on database.table to 'user_name'@'host';
 </code> </code>
 <code MySQL> <code MySQL>
-mysql> create user 'wweb'@'localhost' identified with auth_socket;+mysql> create user 'user_name'@'localhost' identified with auth_socket;
   or   or
-mysql> create user 'wweb'@'localhost' identified with mysql_native_password; +mysql> create user 'user_name'@'localhost' identified with mysql_native_password; 
-mysql> alter user 'wweb'@'localhost' identified with auth_socket;+mysql> alter user 'user_name'@'localhost' identified with auth_socket;
 </code> </code>
 <code MySQL> <code MySQL>
-mysql> create user 'wweb'@'localhost' identified with auth_socket;+mysql> create user 'user_name'@'localhost' identified with auth_socket;
 Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec)
  
Line 183: Line 185:
 The general syntax for granting user privileges is as follows: The general syntax for granting user privileges is as follows:
 <code MySQL> <code MySQL>
-mysql> grant privilege on database.table to 'username'@'host';+mysql> grant privilege on database.table to 'user_name'@'host';
 </code> </code>
 <code MySQL> <code MySQL>
Line 282: Line 284:
 ^performance_schema|aggregates performance-related data about the MySQL server’s operations and resource usage.| ^performance_schema|aggregates performance-related data about the MySQL server’s operations and resource usage.|
 ^sys|a group of stored procedures, functions, and views that are not only critical in providing insights into the MySQL server's performance but also provide a source of information as far as the server's configuration is concerned.| ^sys|a group of stored procedures, functions, and views that are not only critical in providing insights into the MySQL server's performance but also provide a source of information as far as the server's configuration is concerned.|
 +==== password change ====
 <code MySQL> <code MySQL>
 mysql> UPDATE mysql.user SET authentication_string=PASSWORD("password") WHERE User="root"; mysql> UPDATE mysql.user SET authentication_string=PASSWORD("password") WHERE User="root";
  
 mysql> UPDATE mysql.user SET Password=PASSWORD("password") WHERE User="root"; mysql> UPDATE mysql.user SET Password=PASSWORD("password") WHERE User="root";
-</code> 
-<code MySQL> 
-mysql> sudo mysql --user=root --password 
  
-mysql> sudo mysql -u root -p 
-</code> 
-You should obviously change your root password after installation: 
-<code MySQL> 
 mysql> mysqladmin -u root password [newpassword] mysql> mysqladmin -u root password [newpassword]
-</code> 
- 
-<code Bash> 
-$ sudo service mysql stop 
-$ sudo mysqld_safe --skip-grant-tables --skip-networking 
 </code> </code>
  
mysql.1740132955.txt.gz · Last modified: 2025/02/21 19:15 by jianwu