Decompression installation and backup and restore of MySQL in Windows environment

System environment for SERVER2012

1, download the MySQL decompression version, unzip the installation package to the specified directory

2, in the above directory, copy a my-default.ini file, renamed to My.ini, make the following changes (as required):

[mysqld]# Server encoding Method character_set_server=utf8# These is commonly set, remove the # and set as required. #数据文件存放目录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 is faster for transactions and fast selects.# Adjust s Izes as needed, experiment to find the optimal values.# join_buffer_size = 128m# sort_buffer_size = 2m# read_rnd_buffer_si Ze = 2M Sql_mode=no_engine_substitution,strict_trans_tables # allows maximum number of connections max_connections=200

3, add the environment variable, add C:\Program files\mysql\bin to the system environment variable path

4. Execute the command at the command line Mysqld-install

The corresponding uninstall command:mysqld--remove

5. mysql Data initialization

Execute the mysqld--initialize-insecure--user=mysql command to create a root account with a blank password. Note that if you are performing the mysqld--initialize command, a random password user is created.

Initializing the data will result in a data folder in the installation directory, such as:

Because the settings in the above configuration file are DataDir = C:\Program files\mysql\data, the folder name is data.

6, run the net start MySQL start service,

7, set the password. Execute the mysqladmin-u root-p password New password command to set the password for the root user. Here is 123.

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

8. Login

9. View the database

10. Setting Up remote login

View the user table as follows:

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

Such as:

Better refresh the permissions mysql> flush privileges;

Finally restart the MySQL service , you can do remote login (if not remote, restart the general will be resolved).

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

11. Forget password processing

For example, open the configuration file My.ini add skip-grant-tablesbelow mysqld, save the exit and restart the MySQL service .

You can then use Mysql-u root-p to log in without using a password,

After entering the database, 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 My.ini This file, will join the skip-grant-tables This line delete, save exit, restart the MySQL service.

It is worth noting that re-entering the database may encounter a 1820 error that requires resetting the password, such as, this time, only need to execute the command SET PASSWORD = PASSWORD (' 123 '); Can

12. Backup and Restore

To test, create a database MVC

To restore, the syntax is as follows:

Mysql-u root-p [dbname] < Backup.sql

example, restore the MVC database

The command executed is mysql-u root-p MVC < E:\mvc201709120200.sql

Backup, the syntax is as follows:

Backing up multiple tables in a database
Mysqldump-u root-p dbname table1 table2 ... > Bakname.sql

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

example, only one database MVC is backed up, and the character set is specified as UTF8

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

In general, you can use some tools for data backup and restore, such as Workbench

If you want to back up data to a different server on a regular basis, you can use the MYSQLBACKUPFTP software.

Decompression installation and backup and restore of MySQL in Windows environment