After installing MySQL, the data directory must be initialized, includingmysqlTables in system schema: For some MySQL installation methods, data directory initialization is automatically
For other installation methods, you must manually initialize the data directory. These include installation from universal binary and source distributions on Unix and Unix-like systems, and installation from zip compression packages on Windows.
This section describes how to manually initialize the data directory for those MySQL installation methods that cannot automatically initialize the data directory.
Data directory initialization overview
In the example shown here, the server intends tomysqlRun under the user ID of the login account. If the account does not exist, create the account, or replace the name of the different existing login account you plan to use to run the server.
1. Change the location to the top-level directory of MySQL installation, which is usually/usr/local/mysql(adjust the path name of the system as needed):
cd /usr/local/mysql
2. Secure_file_priv system variables limit import and export operations to specific directories. Create a directory whose location can be specified as the value of the variable:
mkdir mysql-files
Grant directory user and group ownershipmysqlUsers andmysqlGroup and set directory permissions appropriately:
chown mysql:mysql mysql-files chmod 750 mysql-files
3. Use the server to initialize the data directory, includingmysql The schema contains the initial MySQL authorization table that determines how users can be allowed to connect to the server. For example:
bin/mysqld --initialize --user=mysql
Normally, data directory initialization only needs to be done after you first install MySQL. However, the command to initialize the data directory will not overwrite any existing onesmysqlschema table, so it is safe to run in any case.
4. If there is no option file, the server runs the default settings. To explicitly specify the options that MySQL server should use at startup, place them in the options file, e.g./etc/or/etc/mysql/. For example, you can use the option file to set the secure_file_priv system variable.
5. Make sure that MySQL can be started without manual intervention when the system starts.
6. The data directory is initialized inmysql Time zone tables are created in schema, but they are not populated.
Data directory initializer
Change the location to the top level directory for MySQL installation, which is usually/usr/local/mysql(adjust the path name of the system as needed):
cd /usr/local/mysql
To initialize the data directory, call mysqld with --initialize or --initialize-insecure options, depending on whether you want the server to generate a random initial password as'root'@'localhost'Account, or password is not specified when creating the account:
1. Use --initialize for a "safe default" installation (i.e., including generating a random initialrootpassword). In this case, the password is marked as expired and you must select a new one.
2. Use --initialize-insecure, it will not be generatedrootpassword. This is not secure; you need to assign a password to your account in a timely manner before putting the server into production.
About allocating new'root'@'localhost'For password description, see root password modification after initialization.
Note: The server writes any message (including any initial password) to its standard error output. This may be redirected to the error log, on Windows, use the --console option to direct messages to the console.
On Unix and Unix-like systems, database directories and files must bemysqlThe login account has so that the server can read and write them later when it runs. To ensure this, pleaserootThe account starts mysqld and includes the --user option as follows:
bin/mysqld --initialize --user=mysql bin/mysqld --initialize-insecure --user=mysql
Or, if logged in asMySQL userExecute mysqld, in which case you can omit the --user option from the command.
On Windows, use one of the following commands:
bin\mysqld --initialize --console bin\mysqld --initialize-insecure --console
Note: If the required system library is missing, the data directory initialization may fail. For example, you may see the following error:
bin/mysqld: error while loading shared libraries: .1: cannot open shared object file: No such file or directory
If this happens, you must manually or use the system's package manager to install the missing library. Then try the data directory initialization command again.
If mysqld does not recognize the correct location of the installation directory or data directory, you may need to specify additional options--basediror--datadir. For example (enter the command in one line):
bin/mysqld --initialize --user=mysql --basedir=/opt/mysql/mysql --datadir=/opt/mysql/mysql/data
Alternatively, place the relevant option settings in the options file and pass the name of the file to mysqld. For Unix and Unix-like systems, assume that the option file name is/opt/mysql/mysql/etc/. Place the following line in the file:
[mysqld] basedir=/opt/mysql/mysql datadir=/opt/mysql/mysql/data
Then call mysqld as follows (enter the command in one line, first enter the --defaults-file option):
bin/mysqld --defaults-file=/opt/mysql/mysql/etc/ --initialize --user=mysql
On Windows, assumingC:\Includes the following lines:
[mysqld] basedir=C:\\Program Files\\MySQL\\MySQL Server 8.4 datadir=D:\\MySQLdata
Then call mysqld as follows (again, you should enter the command in one line, first enter the --defaults-file option):
bin\mysqld --defaults-file=C:\ --initialize --console
Important: When initializing a data directory, no other options should be specified except for the options used to set the directory location (such as --basedir or --datadir) and --user options (if required). The options used by MySQL server during normal use can be set when restarted after initialization. For more information, see the description of the --initialize option.
Server operations during data directory initialization
Note: The data directory initialization sequence performed by the server cannot replace the operations performed by mysql_secure_installation.
When called with the --initialize or --initialize-insecure option, mysqld does the following during the data directory initialization sequence:
1. The server checks whether the data directory exists, as shown below:
If the data directory does not exist, the server creates it.
-
If the data directory exists but is not empty (i.e. contains files or subdirectories), the server exits after an error message is generated:
[ERROR] --initialize specified but the data directory exists. Aborting.
In this case, delete or rename the data directory and try again. If the name of each entry is a period (
.) starts with the allowed inventory data directory to be non-empty.
2. In the data directory, server creationmysqlThe system schema and its tables, including data dictionary tables, authorization tables, time zone tables and server-side help tables.
3. The server initializes the system tablespace and related data structures required to manage InnoDB tables. Note: After setting up the InnoDB system tablespace in mysqld, some changes to the tablespace characteristics require a completely new instance to be set up. Qualified changes include the file name of the first file in the system tablespace and the number of undo logs. If you do not want to use the default values, make sure the settings of the innodb_data_file_path and innodb_log_file_size configuration parameters are in place in the MySQL configuration file, and then run mysqld. Also make sure to specify other parameters that affect InnoDB file creation and location as needed, such as innodb_data_home_dirinnodb_log_group_home_dir. If these options are in the configuration file, but the file is not in the location where MySQL is read by default, use the --defaults-extra-file option to specify the file location when running mysqld.
4. The server creates a 'root'@'localhost' super user account and other retention accounts (see Section 8.2.9, "Retained Account"). Some reserved accounts are locked and cannot be used by the client, but 'root'@'localhost' is for administrative purposes and you should assign a password to it. The server's action on the 'root'@'localhost' account password depends on how you call it up:
Using --initialize but not --initialize-insecure, the server generates a random password, marks it as expired, and writes a message showing the password:
[Warning] A temporary password is generated for root@localhost: iTag*AfrH5ej
Using --initialize-insecure, (regardless of --initialize or not, because --initialize-insecure implies --initialize), the server does not generate a password or marks that the password has expired and writes a warning message:
[Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
For instructions on assigning a new 'root'@'localhost' password, see the instructions below
5. The server fills the server-side help table for HELP. The server does not populate the time zone table. To do this manually
6. If the init_file system variable is used to name a file containing SQL statements, the server will execute the statements in the file. Some features are not available when the server is running in boot mode, which limit the statements allowed in the file. These statements include statements related to account management (such as CREATE USER or GRANT), replication, and global transaction identifiers.
7. The server exits.
The root password allocation after initialization
After initializing the data directory by starting the server with --initialize or --initialize-insecure, start the server normally (i.e. do not use any of these options) and assign a new password to the 'root'@'localhost' account:
1. Start the server
2. Connect to the server
If you initialize the data directory with --initialize but not --initialize-insecure, connect to the server as root:
mysql -u root -p
Then, at the password prompt, enter the random password generated by the server in the initialization sequence:
Enter password: (enter the random root password here)
If you do not know this password, please check the server error log.
If you initialize the data directory with --initialize-insecure, connect to the server as root without password:
mysql -u root --skip-password
3. After connecting, use the ALTER USER statement to assign a new root password:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root-password';
Trying to connect to the host is usually resolved to a localhost account 127.0.0.1. However, if the server runs with skip_name_resolve enabled, it will fail. If you plan to do this, make sure there is an account that can accept connections. For example, to be able to connect as root using --host=127.0.0.1 or --host=::1, create the following account:
CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY 'root-password'; CREATE USER 'root'@'::1' IDENTIFIED BY 'root-password';
These statements can be placed into the file to be executed using the init_file system variable.
This is the article about the implementation steps of MYSQL initialization data directory. For more related contents of MYSQL initialization data directory, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!