Detailed explanation of decompression, installation, backup and restoration of MySQL in Windows environment

This article mainly introduces the decompression, installation, backup and restoration of mysql in the windows environment. Friends in need can refer to the following

The system environment is server2012

1. Download the decompression version of mysql. Unzip the installation package to the specified directory

2. In the above directory, copy a copy of the my-default.ini file, rename it to my.ini, and make the following modifications (according to Required):

[mysqld]
# 服务端的编码方式
character_set_server=utf8
# These are commonly set, remove the # and set as required.
#数据文件存放目录
basedir = C:\Program Files\mysql
#数据文件存放目录
datadir = C:\Program Files\mysql\data
port = 3306
# server_id = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# 允许最大连接数
max_connections=200

3. Add environment variables and add C:\Program Files\mysql\bin to the system’s environment variable Path

4 , execute the command mysqld -install

<img src="https://img.php.cn/upload/article/000/000/194/b2f31546b96637e8c86469c8ce6510de-1.png" alt="">

in the command line and the corresponding uninstall command: mysqld -- remove

5. mysql data initialization

Execute the mysqld --initialize-insecure --user=mysql command to create a root account with an empty password. Note that if the mysqld --initialize command is executed, a random password user will be created.

The initialization data will generate a data folder in the installation directory, as shown below:

Because the setting in the above configuration file is datadir = C:\Program Files\mysql\data, so the folder name is data.

6. Run net start mysql to start the service.

7. Set the password. Execute the mysqladmin -u root -p password new password command to set the password for the root user. Here it is 123.

Note that the original password is entered at Enter password and is empty here.

8. Log in

9. View the database

10. Set up remote login

View the user table, as follows:

Execute the command in the database update user set host = '%' where user = 'root';

As shown below:

It is best to refresh the permissionsmysql> flush privileges;

Finally restart the mysql service and you can log in remotely (if remote login is not possible, restarting will usually solve the problem).

C:\Program Files\mysql\bin>net stop mysql
MySQL 服务正在停止..
MySQL 服务已成功停止。
C:\Program Files\mysql\bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

11. Forgot password processing

As shown below, open the configuration file my.ini, add skip-grant-tables under mysqld, save and exit, and Restart the mysql service.

After that, you can use mysql -u root -p to log in without using a password. After

enter the database and execute use mysql The command switches to the mysql database.

Then execute the following command

update MySQL.user set authentication_string=password('123') where user='root' ;
flush privileges;

After making the changes, re-modify the my.ini file and add the skip-grant-tables Delete this line, save and exit, and restart the mysql service.

It is worth noting that you may encounter an 1820 error when re-entering the database, requiring you to reset your password, as shown below. At this time, you only need to execute the command SET PASSWORD = PASSWORD('123'); That’s it

12. Backup and restore

For testing, create a database mvc

Restore, the syntax is as follows:

mysql -u root -p [dbname] < backup.sql

In the example below, restore the mvc database

The command executed is mysql -u root -p mvc < e:\mvc201709120200.sql

 备份，语法如下：

备份一个数据库多个表

mysqldump -u root -p dbname table1 table2 ... > bakname.sql

备份多个数据库 加上了--databases选项，后面跟多个数据库

mysqldump -u root -p --databases dbname1 dbname2... > bakname.sql

备份所有数据库　

mysqldump -u root -p -all-databases > bakname.sql

 下图中的示例，只备份一个数据库mvc，同时指定字符集为utf8

mysqldump -u root -p --default-character-set=utf8 mvc >e:\mvcbak.sql

 

通常，可以使用一些工具进行数据备份与还原，例如Workbench

如果要定时将数据备份到其他服务器，可以使用mysqlbackupftp这个软件。

总结

The above is the detailed content of Detailed explanation of decompression, installation, backup and restoration of MySQL in Windows environment. For more information, please follow other related articles on the PHP Chinese website!