Common commands for mysql to authorize, start, and start services

Common commands for mysql to authorize, start, and start services
One or four startup methods:

1. mysqld

Start the mysql server :. /mysqld -- defaults-file =/etc/my. cnf -- user = root client connection: mysql -- defaults-file =/etc/my. cnf or mysql-S/tmp/mysql. sock

2. mysqld_safe

Start the mysql server :. /mysqld_safe -- defaults-file =/etc/my. cnf -- user = root & client connection: mysql -- defaults-file =/etc/my. cnf or mysql-S/tm/mysql. sock

3. mysql. server

Cp-v/usr/local/mysql/support-files/mysql. server/etc/init. d/chkconfig -- add mysql. server to start mysql server: service mysql. server {start | stop | restart | reload | force-reload | status} client connection: Same as 1 and 2

4. mysqld_multi

Mkdir $ MYSQL_BASE/data2cat <-EOF>/etc/my. cnf [mysqld_multi] mysqld =/usr/local/mysql/bin/mysqld_safemysqladmin =/user/local/mysql/bin/mysqladminuser = mysqladminpassword = mysqladmin [mysqld3306] port = 3306 socket =/ tmp/mysql3306.sockpid-file =/tmp/bytes-external-lockingkey_buffer_size = bytes = 1Mtable_open_cache = 64sort_buffer_size = bytes = 8 Mbasedir =/usr/local/mysqldatadir =/usr/ local/mysql/data [mysqld3307] port = 3307 socket =/tmp/mysql3307.sockpid-file =/tmp/bytes-external-lockingkey_buffer_size = bytes = 1Mtable_open_cache = 64sort_buffer_size = bytes = 8Kread_buffer_size = bytes = 8 Mbasedir =/usr/local/mysqldatadir =/usr/local/mysql/data2EOF # mysql-S/tmp/mysql3306.sockmysql> grant shutdown on *. * TO 'mysqladmin' @ 'localhost' identified by 'mysqladmin' with grant option; # mysql-S/tmp/mysql3307.sockmysql> grant shutdown on *. * TO 'mysqladmin' @ 'localhost' identified by 'mysqladmin' with grant option; start the mysql server :. /mysqld_multi -- defaults-file =/etc/my. cnf start 3306-3307 shut down the mysql server: mysqladmin shutdown

Ii. Start and close the mysql Service

1. In windows:

Start: mysqld -- console or net start mysql close: mysqladmin-u root shutdown or net stop mysql

In linux:

Start: service mysql start stop: service mysql stop restart service: service mysql restart

3. Create a user to assign Permissions

1. Create a user named "buff" with the password "buff"

// Log on to MySQLmysql-uroot-pEnter password as the root user: // create mysql> insert into mysql. user (Host, User, Password) values ("localhost", "buff", password ("buff"); // refresh the system permission table mysql> flush privileges;

Logon Test

Mysql> exit // log on to MySQLmysql-ubuff-pEnter password: mysql> // the newly created user buff is successfully logged on.

User authorization

// Log on to MySQLmysql-uroot-pEnter password as the root user: // create a database bluebuffmysql> create database bluebuff for the user buff; // authorize the user buff to have all the permissions of the database bluebuff mysql> grant all privileges on bluebuff. * to buff @ localhost identified by 'buff'; mysql> flush privileges;

Logon Test

// Log on to the mysql-ubuff-pEnter privileges database with the user buff: // display the database mysql> show databases;

The result is shown in, indicating that buff authorization is successful.

5. Modify the user's buff Password

// Log on to MySQLmysql-uroot-pEnter password as the root user: // modify the password of your buff mysql> update table mysql. user set password = password ('buffer') where User = 'buffer' and Host = 'localhost'; mysql> flush privileges;

6. delete a user

// Log on to MySQLmysql-uroot-pEnter password as the root user: // delete user buffmysql> delete from mysql. user where User = 'buffer' and Host = 'localhost'; mysql> flush privileges;

7. delete a database

mysql>drop database bluebuff;

4. view the permissions granted by the user

In mysql, permissions granted to users may be divided into global permissions, database-level permissions, table-level permissions, column-level permissions, and subprogram-level permissions.

1. Global Level:

Global permissions apply to all databases on a given server. These permissions are stored in the mysql. user table. Grant all on *. * and revoke all on *. * only GRANT and REVOKE Global permissions. Example:. create a test account and grant the global permissions mysql> grant select, insert on *. * to test @ '%' identified by 'test'; mysql> flush privileges; B. query the permissions granted to test: show grants for test; select * from mysql. user where user = 'test' \ G;

2. Database level:

The database Permission applies to all targets in a given database. These permissions are stored in the mysql. db and mysql. host tables. Grant all on db_name. * and revoke all on db_name. * Only grant and revoke database permissions. Example:. create a test account and grant the database-level permission drop user test; grant select, insert, update, delete on MyDB. * to test @ '%' identified by 'test'; B. query the select * from mysql. user where user = 'test' \ G; -- you can see that no select * from mysql is authorized. db where user = 'test' \ G; show grants for test;

3. surface level:

The table Permission applies to all columns in a given table. These permissions are stored in the mysql. tables_priv table. Grant all on db_name.tbl_name and revoke all on db_name.tbl_name only GRANT and REVOKE table permissions. Example:. create a test account named test and grant the table-level permission to drop user test; flush privileges; grant all on MyDB. kkk to test @ '%' identified by 'test'; B. query the permissions granted to test: show grants for test; select * from mysql. tables_priv \ G;

4. Column level:

The column Permission applies to a single column in a given table. These permissions are stored in the mysql. columns_priv table. When using REVOKE, you must specify the same columns as the authorized columns. Example:. create a test account named test and grant the column-level permission to drop user test; flush privileges; grant select (id, col1) on MyDB. TEST1 to test @ '%' identified by 'test'; flush privileges; B. query the select * from mysql. columns_priv; show grants for test;

5. subroutine level:

The create routine, alter routine, EXECUTE, and GRANT permissions apply to stored subroutines. These permissions can be granted at the global level and database level. In addition to create routine, these permissions can be granted as a subroutine level and stored in the mysql. procs_priv table. Example:. CREATE a test account named test and grant the sub-Program-level permission to drop procedure if exists PRC_TEST; DELIMITER // create procedure PRC_TEST ()-> BEGIN-> SELECT * FROM kkk; -> END // DELIMITER; grant execute on MyDB. PRC_TEST to test @ '%' identified by 'test'; flush privileges; B. query the permissions granted to test: show grants for test; select * from mysql. procs_priv where User = 'test ';

Summary:

1. To view the permissions granted to a user, you need to view the permissions granted from these five levels. From top to bottom or from small to top, check the permissions granted to each level one by one.

2. grant create routine, alter routine, select, create, insert, update, delete, execute on ....

3. If the client cannot connect to the server, check whether the host item in the user table is '%' and has been authorized