Install, back up, and restore mysql in windows, and decompress mysql.

Install, back up, and restore mysql in windows, and decompress mysql.

 

The system environment is server2012.

1. Download the mysql decompress version and unzip the installation package to the specified directory.

2. In the above directory, copy a my-default.ini file, rename it to my. ini, and make the following changes (as needed ):

[Mysqld] # 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 \ dataport = 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 = 128 M # sort_buffer_size = 2 M # read_rnd_buffer_size = 2 M SQL _mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES # maximum number of connections allowed max_connections = 200

3. Add the environment variable C: \ Program Files \ mysql \ bin to the system environment variable Path.

4. Execute the mysqld-install command in the command line.

Corresponding uninstall command: mysqld -- remove

5. mysql DATA Initialization

RunMysqld -- initialize-insecure -- user = mysqlCommand to create a root account with an empty password. Note: If the execution isMysqld -- initializeCommand to create a random password user.

The initialization data will generate a data folder in the installation directory, for example:

Because the above configuration file is setDatadir = C: \ Program Files \ mysql \ data, So the folder name is data.

 

6. Run net start mysql to start the service,

 

7. Set the password.RunMysqladmin-u root-p password New passwordCommand to set the password for the root user. 123.

Note: The original password is entered at Enter password, which is null here.

 

8. log on

 

9. view the database

 

 10. Set remote Logon

View the user table as follows:

 

Execute commands in the databaseUpdate user set host = '%' where user = 'root ';

For example:

You 'd better refresh the permission.Mysql> flush privileges;

LastRestart the mysql ServiceYou can log on remotely. (If you cannot log on remotely, restart the system ).

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

11. forgot password processing

For example, open the configuration file my. ini and add it under mysqld.Skip-grant-tables, Save and exit,And restart the mysql service..

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

Then enter the database and runUse mysqlCommand to switch to the mysql database.

Run the following command:

Update MySQL. user set authentication_string = password ('20140901') where user = 'root';

Flush privileges;

After modification, modify the my. ini file and addSkip-grant-tablesThis line is deleted, saved and exited, And the mysql service is restarted.

It is worth noting that a 1820 error may occur when you re-enter the database. You must reset the password. For example, you only need to execute the commandSet password = PASSWORD ('20140901 ');You can.

 

12. Backup and Restoration

Create a database mvc for testing

 

Syntax:

Mysql-u root-p [dbname] <backup. SQL

To restore the mvc database.

The command isMysql-u root-p mvc <e: \ mvc201709120200. SQL

The backup syntax is as follows:

Back up multiple tables in a database
Mysqldump-u root-p dbname table1 table2...> bakname. SQL

Backup Multiple databases with 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

For example, back up only one database mvc and specify the character set as utf8

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

Usually, you can use some tools for data backup and restoration, such as Workbench

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