1. not allow grant (mysql 8.x)
You are not allowed to create a user with GRANT
MYSQL8.0版本中，**grant**已經做了一些限制，需要增加子用戶。
2. create user with ‘localhost’ limited.
create user 'username'@'localhost' identified by 'your password';
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
3. first install the mysql-server ‘s log
after you installed the my-server(8.0) ,the /var/log/mysql/error.log ‘s content as below :
2024-06-01T10:47:53.303275Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.36-0ubuntu0.22.04.1) initializing of server in progress as process 2524495
2024-06-01T10:47:53.308841Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-06-01T10:47:53.621206Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-06-01T10:47:55.308728Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2024-06-01T10:47:56.361302Z 6 [System] [MY-013172] [Server] Received SHUTDOWN from user boot. Shutting down mysqld (Version: 8.0.36-0ubuntu0.22.04.1).
2024-06-01T10:47:59.702238Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.36-0ubuntu0.22.04.1) starting as process 2524540
2024-06-01T10:47:59.723628Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-06-01T10:47:59.959131Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-06-01T10:48:00.209342Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-06-01T10:48:00.209368Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-06-01T10:48:00.213346Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/tmp' in the path is accessible to all OS users. Consider choosing a different directory.
2024-06-01T10:48:00.239500Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: /var/run/mysqld/mysqlx.sock
2024-06-01T10:48:00.239617Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.36-0ubuntu0.22.04.1' socket: '/tmp/tmp.x2knLvB42L/mysqld.sock' port: 0 (Ubuntu).
2024-06-01T10:48:00.280022Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.36-0ubuntu0.22.04.1).
2024-06-01T10:48:01.727897Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.36-0ubuntu0.22.04.1) (Ubuntu).
2024-06-01T10:48:02.636676Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.36-0ubuntu0.22.04.1) starting as process 2524609
2024-06-01T10:48:02.643704Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-06-01T10:48:02.832343Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-06-01T10:48:03.028042Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-06-01T10:48:03.028072Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-06-01T10:48:03.032569Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/tmp' in the path is accessible to all OS users. Consider choosing a different directory.
2024-06-01T10:48:03.051732Z 7 [System] [MY-013172] [Server] Received SHUTDOWN from user boot. Shutting down mysqld (Version: 8.0.36-0ubuntu0.22.04.1).
2024-06-01T10:48:03.057668Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2024-06-01T10:48:04.552078Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.36-0ubuntu0.22.04.1) (Ubuntu).
about the ‘—initialize-insecure’ introduction document link is Here.
https://dev.mysql.com/doc/refman/8.0/en/server-options.html#option_mysqld_initialize-insecure
if you want use the mysqld —initialize command to generate mysql database password, you may get the error in the /var/log/mysql/error.log :
[Server] --initialize specified but the data directory has files in it. Aborting.
so ,you must delete the data of the mysql data dir /var/lib/mysql
then exec the command : mysqld —initialize
then you can get the mysql password in the log (/var/log/mysql/error.log);
2024-11-09T08:08:18.311176Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: /nuepoLfZ3=q
then start the mysql :
sudo service mysql restart
or
/etc/init.d/mysql start
use the random password login mysql .
when you exec the mysql command in mysql line mode, you will get the error :
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
try to use ‘alter user [root@localhost](<mailto:root@localhost>) identified by 'your new password' ’ to set the root ‘s password.
4. can’t create lock file /var/run/mysqld/mysqlx.sock.lock’
[ERROR] [MY-011300] [Server] Plugin mysqlx reported: 'Setup of socket: '/var/run/mysqld/mysqlx.sock' failed, can't create lock file /var/run/mysqld/mysqlx.sock.lock'
不能夠創建myqlx.sock.lock文件:
A message intended for a client cannot be sent there as no client-session is attached. Therefore, we’re sending the information to the error-log instead: MY-000029 – File ‘/usr/local/src/mysql_init‘ not found (OS errno 13 – Permission denied)
5. MSYQL reset root password
On Unix, use the following procedure to reset the password for the MySQL 'root'@'localhost' account. To change the password for a root account with a different host name part, modify the instructions to use that host name.
The instructions assume that you start the MySQL server from the Unix login account that you normally use for running it. For example, if you run the server using the mysql login account, you should log in as mysql before using the instructions. Alternatively, you can log in as root, but in this case you must start mysqld with the --user=mysql option. If you start the server as root without using --user=mysql, the server may create root-owned files in the data directory, such as log files, and these may cause permission-related problems for future server startups. If that happens, you must either change the ownership of the files to mysql or remove them.
- Log on to your system as the Unix user that the MySQL server runs as (for example,
mysql). - Stop the MySQL server if it is running. Locate the
.pidfile that contains the server’s process ID. The exact location and name of this file depend on your distribution, host name, and configuration. Common locations are/var/lib/mysql/,/var/run/mysqld/, and/usr/local/mysql/data/. Generally, the file name has an extension of.pidand begins with eithermysqldor your system’s host name. Stop the MySQL server by sending a normalkill(notkill -9) to the mysqld process. Use the actual path name of the.pidfile in the following command:
$> kill cat /mysql-data-directory/host_name.pid“ Use backticks (not forward quotation marks) with the cat command. These cause the output of cat to be substituted into the kill command. 3. Create a text file containing the password-assignment statement on a single line. Replace the password with the password that you want to use.
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass'; 4. Save the file. This example assumes that you name the file /home/me/mysql-init. The file contains the password, so do not save it where it can be read by other users. If you are not logged in as mysql (the user the server runs as), make sure that the file has permissions that permit mysql to read it. 5. Start the MySQL server with the init_file system variable set to name the file:
$> mysqld --init-file=/home/me/mysql-init & The server executes the contents of the file named by the init_file system variable at startup, changing the 'root'@'localhost' account password. Other options may be necessary as well, depending on how you normally start your server. For example, --defaults-file may be needed before the init_file argument. 6. After the server has started successfully, delete /home/me/mysql-init.
You should now be able to connect to the MySQL server as root using the new password. Stop the server and restart it normally.
MySQL :: MySQL 8.0 Reference Manual :: B.3.3.2 How to Reset the Root Password
6. exec mysqld —init-file=/home/ubuntu/mysql_init
the error log is :
2024-06-02T04:26:29.419188Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.36-0ubuntu0.22.04.1) (Ubuntu).
2024-06-02T04:28:01.567754Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.36-0ubuntu0.22.04.1) starting as process 28222
2024-06-02T04:28:01.574355Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2024-06-02T04:28:01.574414Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2024-06-02T04:28:01.574423Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-06-02T04:28:01.574754Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.36-0ubuntu0.22.04.1) (Ubuntu).
I delete all files of the dir /var/lib/mysql , as below :
initObject fail after 3 times case MySQL8 caching_sha2_password authentication plugin need enable OpenSSL support
7. Mysql Server for Mac M1
start the mysql server .
You can go to the system settings . There is a MYSQL icon on the left side ,then click the Item. you will get the UI:
click the button 【Start MySQL Server 】 then Mysql will start .
When you use the brew start the MYSQL Server.
brew services start mysql
then you will get the result .
Warning: Taking root:admin ownership of some mysql paths:
/opt/homebrew/Cellar/mysql/8.3.0_1/bin
/opt/homebrew/Cellar/mysql/8.3.0_1/bin/mysqld_safe
/opt/homebrew/opt/mysql
/opt/homebrew/opt/mysql/bin
/opt/homebrew/var/homebrew/linked/mysql
This will require manual removal of these paths using `sudo rm` on
brew upgrade/reinstall/uninstall.
Warning: mysql must be run as non-root to start at user login!
==> Successfully started `mysql` (label: homebrew.mxcl.mysql)
Then try connect the mysql Server .
ubuntu@hostname_mac Downloads % mysql -uroot -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
The MYSQL Server doesn’t start .
Summary