WIN10 installing the MySQL process && linking process && backing up and importing data &&grant commands
First, installation
The latest version 8.0 downloaded at the beginning of the MySQL official website (https://www.mysql.com/downloads/ ), causing the management tool to not link.
Second, after trying the 5.7 version. Because it is not a direct install version, you need to run the install on the win command interface. You still experience the following issues:
1, need to find the Windows system in the Start column--command prompt--right-click Run as Administrator.
2, in the Start column-open Windows management tools-services, found in the MySQL process, click Start, but error cannot start.
3. The MySQL service cannot be started after restarting the computer. (This is also a version issue).
Third, finally in the official website download version 5.6, follow the above steps two operation successfully started the MySQL service.
Iv. Links:
Management tool link MySQL, with 127.0.0.1 access to success, with the other IP is not, this need to create a MySQL user, to restrict access to the IP, the test with a wildcard, so that no restrictions on the access of other IP addresses (the actual production environment can not be so!! ）。
The command to be used;
GRANT all privileges on *. * to ' root ' @ ' * ' identified by ';
GRANT all privileges on * * to ' root ' @ '% ' identified by ';
FLUSH privileges;
Five, import data:
Six, backup data;
[MySQL]-MySQL's Grant command
Source:http://yingxiong.javaeye.com/blog/451208
This example runs on MySQL version 5.0 and above.
The simple format that MySQL gives to user rights commands can be summarized as:
grant permissions on database object to user
A grant general data user, the right to query, insert, UPDATE, delete all table data in the database.
Grant SELECT on testdb.* to[email protected]‘%‘
Grant insert on testdb.* to[email protected]‘%‘
Grant Update on testdb.* to[email protected]‘%‘
Grant Delete on testdb.* to[email protected]‘%‘
Alternatively, replace it with a MySQL command:
Grant SELECT, INSERT, UPDATE, delete on testdb.* to[email protected]‘%‘
II. Grant Database Developer, creating tables, indexes, views, stored procedures, functions ... and other permissions.
Grant creates, modifies, and deletes MySQL data table structure permissions.
Grant Create on testdb.* to[email protected]' 192.168.0.% ';
Grant ALTER on testdb.* to[email protected]' 192.168.0.% ';
Grant drop on testdb.* to[email protected]' 192.168.0.% ';
Grant operates MySQL foreign key permissions.
Grant references on testdb.* to[email protected]' 192.168.0.% ';
Grant operates MySQL temp table permissions.
Grant create temporary tables on testdb.* to[email protected]' 192.168.0.% ';
Grant operates MySQL index permissions.
Grant Index on testdb.* to[email protected]' 192.168.0.% ';
Grant operates the MySQL view, viewing the view source code permissions.
Grant CREATE view on testdb.* to[email protected]' 192.168.0.% ';
Grant Show view on testdb.* to[email protected]' 192.168.0.% ';
Grant operates MySQL stored procedures, function permissions.
Grant create routine on testdb.* to[email protected]' 192.168.0.% '; --now, can show procedure status
Grant alter routine on TESTDB.* to[email protected]' 192.168.0.% '; --now, can drop a procedure
Grant execute on testdb.* to[email protected]' 192.168.0.% ';
Grant General DBA manages permissions for a MySQL database.
Grant all privileges the TestDB to[email protected]' localhost '
Where the keyword "privileges" can be omitted.
Grant Advanced DBA manages permissions for all databases in MySQL.
Grant all on * * to [email protected] ' localhost '
MySQL grant permissions can be used on multiple levels, respectively.
1. Grant acts on the entire MySQL server:
Grant SELECT On *. * to [email protected]; --DBAs can query tables in all databases in MySQL.
Grant all on * * to [email protected]; --DBA can manage all databases in MySQL
2. Grant acts on a single database:
Grant Select on testdb.* to [email protected]; --DBAs can query the tables in TestDB.
3. Grant acts on a single data table:
Grant SELECT, INSERT, UPDATE, delete on testdb.orders to [email protected];
Here, you can execute the above statement multiple times when you grant multiple tables to a user. For example:
Grant Select (user_id,username) on smp.users to [e-mail protected] '% ' identified by ' 123345 ';
Grant Select on smp.mo_sms to [email protected] '% ' identified by ' 123345 '; 4. Grant acts on the columns in the table:
Grant Select (ID, SE, rank) on testdb.apache_log to [email protected];
5. Grant acts on stored procedures, functions:
Grant execute on procedure testdb.pr_add to ' dba ' @ ' localhost '
Grant execute on function testdb.fn_add to ' dba ' @ ' localhost '
Vi. Viewing MySQL user rights
To view the current user (own) permissions:
Show grants;
To view additional MySQL user rights:
Show grants for [email protected];
Vii. revoke permissions that have been given to MySQL users.
Revoke is similar to Grant's syntax, just replace the keyword "to" with "from":
Grant all on * * to [email protected];
Revoke all on * * from [email protected];
Viii. MySQL Grant, REVOKE user rights considerations
1. Grant, after revoke user rights, the user has to reconnect to the MySQL database for the permission to take effect.
2. If you want to allow authorized users, you can also grant these permissions to other users, you need the option "grant option"
Grant Select on testdb.* to [e-mail protected] with GRANT option;
This feature is generally not available. In practice, database permissions are best managed centrally by DBAs.
*************************************************************************************************
Encountered SELECT command denied to user ' username ' @ ' hostname ' for table ' name ' This error, the workaround is to have the table name behind the authorization, that is, you want to authorize the core database.
I met the select command denied to the user ' my ' @ '% ' for table ' proc ', is the time to call the stored procedure, the original thought that as long as the specified database authorization on the line, what stored procedures, functions, etc. are no longer tube, Who knows also to have the database MySQL proc table authorized
*************************************************************************************************
Reference: http://zhidao.baidu.com/question/19633785.html
There are 5 tables in the MySQL authorization table: User, DB, host, Tables_priv, and Columns_priv.
The contents of the authorization form are used as follows:
User table
The user table lists the users and their passwords that can connect to the server, and it specifies what global (superuser) permissions they have. Any permissions that are enabled on the user table are global permissions and apply to all databases. For example, if you have delete permission enabled, the users listed here can delete records from any table, so think carefully before you do so.
DB table
The DB table lists the databases, and the user has permission to access them. The permissions specified here apply to all tables in a database.
Host table
The host table is used in conjunction with the DB table to control access to the database for a particular host at a better level, which may be better than using DB alone. This table is not affected by the grant and REVOKE statements, so you may find that you are not using it at all.
Tables_priv table
The Tables_priv table specifies table-level permissions, and one of the permissions specified here applies to all columns of a table.
Columns_priv table
The COLUMNS_PRIV table specifies column-level permissions. The permissions specified here apply to a specific column of a table.
Win10 Installing the MySQL process && linking process && backing up and importing data &&grant commands
