Common commands for mysql authorization, startup, and service startup

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

Start 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

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

mkdir $MYSQL_BASE/data2
cat <<-EOF>> /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /user/local/mysql/bin/mysqladmin
user = mysqladmin
password = mysqladmin

[mysqld3306]
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /tmp/mysql3306.pid
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data

[mysqld3307]
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /tmp/mysql3307.pid
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data2
EOF

#mysql -S /tmp/mysql3306.sock
mysql>GRANT SHUTDOWN ON *.* TO 'mysqladmin'@'localhost' identified by 'mysqladmin' with grant option;

#mysql -S /tmp/mysql3307.sock
mysql>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

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

Start: service mysql start
Stop: service mysql stop
Restart the service: service mysql restart

//root user logs in to MySQL
mysql -uroot -p
Enter password:
//Create a new usermysql>insert into mysql.user(Host,User,Password) values("localhost","buff",password("buff"));
// Refresh the system privileges table mysql>flush privileges;

mysql>exit
// User buff logs in to MySQL
mysql -ubuff -p
Enter password:
mysql>
// Indicates that the newly created user buff has logged in successfully

//root user logs in to MySQL
mysql -uroot -p
Enter password:
// Create a database bluebuff for user buff
mysql>create database bluebuff;
// Authorize user buff to have all privileges of database bluebuff mysql>grant all privileges on bluebuff.* to buff@localhost identified by 'buff';
mysql>flush privileges;

// User buff logs in to the database mysql -ubuff -p
Enter privileges:
// Display databasesmysql>show databases;

//root user logs in to MySQL
mysql -uroot -p
Enter password:
//Change the password of user buffmysql>update table mysql.user set password=password('buffer') where User='buff' and Host='localhost';
mysql>flush privileges;

//root user logs in to MySQL
mysql -uroot -p
Enter password:
// Delete user buff
mysql>delete from mysql.user where User = 'buff' and Host = 'localhost';
mysql>flush privileges;

mysql>drop database bluebuff;

Global privileges apply to all databases in a given server. These permissions are stored in the mysql.user table. GRANT ALL ON *.* and REVOKE ALL ON *.* grant and revoke only global privileges.

Example: a. Create a test account test and grant 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;

Database privileges apply to all objects 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: a. Create a test account test and grant database-level permissions 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 select * from mysql.db where user='test'\G;
show grants for test;

Table privileges apply to all columns in a given table. These privileges are stored in the mysql.tables_priv table. GRANT ALL ON db_name.tbl_name and REVOKE ALL ON db_name.tbl_name grant and revoke table privileges only.

Example: a. Create a test account test and grant table-level permission 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;

Column permissions apply to a single column in a given table. These privileges are stored in the mysql.columns_priv table. When using REVOKE, you must specify the same columns as those being granted.

Example: a. Create a test account test and grant column-level permission 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;

CREATE ROUTINE, ALTER ROUTINE, EXECUTE, and GRANT privileges apply to stored routines. These privileges can be granted at the global level or at the database level. Also, except CREATE ROUTINE, these privileges can be granted at the subprogram level and are stored in the mysql.procs_priv 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';