Decompression, installation, backup and restore of MySQL under Windows environment

The system environment is server2012

1. Download the decompressed version of MySQL and decompress 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 changes (as needed):

[mysqld]
# The encoding method of the server is character_set_server=utf8
# These are commonly set, remove the # and set as required.
#Data file storage directory basedir = C:\Program Files\mysql
#Data file storage directory 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
# Maximum number of connections allowed 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 in the command line

The corresponding uninstall command: mysqld --remove

5. MySQL data initialization

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

Initializing the 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 , the folder name is data.

6. Run net start mysql to start the service.

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

Note that the original password is entered at Enter password, and this place is empty.

8. Login

9. View the database

10. Set up remote login

View the user table, as follows:

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

As shown below:

It is best to refresh the privileges mysql> flush privileges;

Finally, restart the MySQL service and you can log in remotely (if you cannot log in remotely, restarting it will usually solve the problem).

C:\Program Files\mysql\bin>net stop mysql
MySQL service is stopping..
The MySQL service has been stopped successfully.
C:\Program Files\mysql\bin>net start mysql
The MySQL service is starting.
The MySQL service has been started successfully.

11. Forgot password handling

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

You can then use mysql -u root -p to log in without a password.

Then enter the database and execute the use mysql command to switch to the mysql database.

Then execute the following command

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

After the change, re-modify the my.ini file, delete the skip-grant-tables line, save and exit, and restart the MySQL service.

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

12. Backup and restore

For testing, create a database mvc

To restore, the syntax is as follows:

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

The following figure shows an example of restoring the mvc database.

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

Backup, the syntax is as follows:

Back up multiple tables in a database

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

To back up multiple databases, add the --databases option followed by multiple databases.

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

Back up all databases

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

The example in the figure below only backs up one database mvc and specifies the character set as utf8

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

Usually, you can use some tools to back up and restore data, such as Workbench

If you want to back up data to other servers regularly, you can use the mysqlbackupftp software.

Summarize

The above is the introduction of decompression, installation, backup and restoration of MySQL under Windows environment. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!