Shulou(Shulou.com)06/01 Report--

First, there are four startup modes:

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 starts mysql server: service mysql.server {start | stop | restart | reload | force-reload | status} client connection: same as 1,2

4 、 mysqld_multi

Mkdir $MYSQL_BASE/data2cat / etc/ my.cnf [mysqld _ multi] mysqld = / usr/local/mysql/bin/mysqld_safemysqladmin = / user/local/mysql/bin/mysqladminuser = mysqladminpassword = mysqladmin [mysqld3306] port = 3306socket = / tmp/mysql3306.sockpid-file = / tmp/mysql3306.pidskip-external-lockingkey_buffer_size = 16Mmax_allowed_packet = 1Mtable_open_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam _ Sort_buffer_size = 8Mbasedir = / usr/local/mysqldatadir = / usr/local/mysql/ data [mysqld3307] port = 3307socket = / tmp/mysql3307.sockpid-file = / tmp/mysql3307.pidskip-external-lockingkey_buffer_size = 16Mmax_allowed_packet = 1Mtable_open_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8Mbasedir = / 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 mysql server:. / mysqld_multi-- defaults-file=/etc/my.cnf start 3306-3307 shut down mysql server: mysqladmin shutdown II. Start and shut down mysql service

1. Under windows:

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

Under linux:

Start: service mysql start stop: service mysql stop restart service: service mysql restart 3. Create users to assign permissions

1. New user: create a user named: buff with password: buff

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

Login test

Mysql > exit// user buff login MySQLmysql-ubuff-pEnter password:mysql > / / indicates that the newly created user buff login is successful

User authorization

/ / root users log in to MySQLmysql-uroot-pEnter password:// to create a database for user buff bluebuffmysql > create database bluebuff;// authorized user buff has all the permissions of database bluebuff mysql > grant all privileges on bluebuff.* to buff@localhost identified by 'buff';mysql > flush privileges

Login test

/ / user buff login database mysql-ubuff-pEnter privileges:// display database mysql > show databases

The result is shown in the following figure, indicating that the authorization for the user buff is successful.

5. Modify the password of user buff

/ / root users log in to MySQLmysql-uroot-pEnter password:// to change the password of user buff mysql > update table mysql.user set password=password ('buffer') where User='buff' and Host='localhost';mysql > flush privileges

6. Delete a user

/ / root users log in to MySQLmysql-uroot-pEnter password:// to delete users buffmysql > delete from mysql.user where User = 'buff' and Host =' localhost';mysql > flush privileges

7. Delete the database

Mysql > drop database bluebuff; 4. View the permissions granted by the user

In mysql, the permissions granted to users may be divided into global level permissions, database level permissions, surface level permissions, column level permissions, and subroutine level permissions.

1. Global level:

Global permissions apply to all databases in 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: a. Create a test account test and grant permissions at the global level 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:

Database permissions apply 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 for example: a. Create a test account, test, and grant database-level permissions to drop user test;grant select,insert,update,delete on MyDB.* to test@'%' identified by 'test';b. Query the permissions granted to test select * from mysql.user where user='test'\ G;-- you can see that there is no authorization for select * from mysql.db where user='test'\ Ghost show grants for test

3. Surface level:

Table permissions apply to all columns in a given table. These permissions are stored in the mysql.tables_ private table. GRANT ALL ON db_name.tbl_name and REVOKE ALL ON db_name.tbl_name only grant and revoke table permissions. Example: a. Create a test account test and grant layer-level permissions 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:

Column permissions apply to a single column in a given table. These permissions are stored in the mysql.columns_ private table. When using REVOKE, you must specify the same column as the authorized column. Example: a. Create a test account test and grant column-level permissions drop user test;flush privileges;grant select (id, col1) on MyDB.TEST1 to test@'%' identified by 'test';flush privileges;b. Query the permissions granted to test select * from mysql.columns_priv;show grants for test

5. Subroutine level:

CREATE ROUTINE, ALTER ROUTINE, EXECUTE and GRANT permissions apply to stored subroutines. These permissions can be granted at the global and database levels. Moreover, in addition to CREATE ROUTINE, these permissions can be granted at the subroutine level and stored in the mysql.procs_ private table. Example: a. Create a test account test and grant subroutine-level permissions 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. If you need to see the permissions granted by the user, you need to view the granted permissions from these five levels. Check the permissions granted at each level from top to bottom or from small to top.

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

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.