Start or stop
Start Mysql Server
sudo /usr/local/mysql/support-files/mysql.server start
Start the Mysql client
sudo /usr/local/mysql/support-files/mysql.server stop
mac preference settings start or close
Configuration file settings
Copy cnf files
cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf //Copy the default mysql configuration file to the / etc directory
Configuration file path / etc/my.cnf
[mysqld] character-set-server=utf8 collation-server=utf8_general_ci [client] # Server settings, terminal input mysql without input password coding settings default-character-set=utf8 user='root' password='456' [mysql] default-character-set=utf8
After the configuration is completed, the server needs to be restarted to take effect.
Environmental variable configuration
Add,. profile or. zshrc to your corresponding configuration file
PATH=$PATH:/usr/local/mysql/bin
Change Password
update mysql.user set authentication_string=password('123') where user='root' and host='localhost'; flush privileges; # Write settings
Create Users and Authorizations
Authorized interpretation
Authorization table user # The permission to release the table for: all data, all tables under all libraries, and all fields under the table db tables_priv# Permission to release the table. For: a table and all fields under it columns_priv Explain as follows: user: release db1, db2, and all that it contains Db: Release db1, and all of its db1 components tables_priv: Release db1.table1, and all that it contains Column_prive: Release db1.table1.column1, only this field
Create user
create user 'buyi'@'1.1.1.1' identified by '123'; create user 'buyi'@'192.168.1.%' identified by '123'; create user 'buyi'@'%' identified by '123';
To grant authorization
Authorization: Permission to folders, files, a field of a file View Help: help grant Common permissions are: select,update,alter,delete All can represent all rights except grant. Authorization for all libraries: *..* Grant select on *. * to'buyi1'@'localhost' identified by'123'; Only in the user table can you find that the selectable permission of buyi1 user is set to Y For a database: db1..* Grant select on db1. * to'buyi2'@'%' identified by'123'; Only in the DB table can you find that the selectable permission of buyi2 user is set to Y For a table: db1.t1 Grant select on db1.t1 to'buyi3'@'%' identified by'123'; Select permissions for buyi3 users can be found only in tables_priv table For a field: mysql> select * from t3; +------+-------+------+ | id | name | age | +------+-------+------+ | 1 | buyi1 | 18 | | 2 | buyi2 | 19 | | 3 | buyi3 | 29 | +------+-------+------+ grant select (id,name),update (age) on db1.t3 to 'buyi4'@'localhost' identified by '123'; You can see the corresponding permissions in tables_priv and columns_priv mysql> select * from tables_priv where user='buyi4'\G mysql> select * from columns_priv where user='buyi4'\G
Delete permission
revoke select,update on db1.student from 'lili'@localhost;
mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select host,user from user; +-----------+-----------+ | host | user | +-----------+-----------+ | % | buyi | | localhost | jack | | localhost | lili | | localhost | mysql.sys | | localhost | root | | localhost | tom | +-----------+-----------+ 6 rows in set (0.00 sec) mysql> select * from tables_priv; +-----------+-----+-----------+------------+----------------+---------------------+---------------+---------------+ | Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv | +-----------+-----+-----------+------------+----------------+---------------------+---------------+---------------+ | localhost | sys | mysql.sys | sys_config | root@localhost | 2017-09-04 11:37:51 | Select | | | localhost | db1 | lili | student | root@localhost | 0000-00-00 00:00:00 | Select,Update | | | localhost | db1 | jack | teacher | root@localhost | 0000-00-00 00:00:00 | Select | | | localhost | db1 | tom | course | root@localhost | 0000-00-00 00:00:00 | | Select,Update | +-----------+-----+-----------+------------+----------------+---------------------+---------------+---------------+ 4 rows in set (0.00 sec) mysql> select * from columns_priv; +-----------+-----+------+------------+-------------+---------------------+---------------+ | Host | Db | User | Table_name | Column_name | Timestamp | Column_priv | +-----------+-----+------+------------+-------------+---------------------+---------------+ | localhost | db1 | tom | course | name | 0000-00-00 00:00:00 | Select,Update | | localhost | db1 | tom | course | cycle | 0000-00-00 00:00:00 | Select,Update | +-----------+-----+------+------------+-------------+---------------------+---------------+ 2 rows in set (0.00 sec) mysql> revoke select,update on db1.student from 'lili'@localhost; Query OK, 0 rows affected (0.00 sec) mysql> select * from tables_priv; +-----------+-----+-----------+------------+----------------+---------------------+------------+---------------+ | Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv | +-----------+-----+-----------+------------+----------------+---------------------+------------+---------------+ | localhost | sys | mysql.sys | sys_config | root@localhost | 2017-09-04 11:37:51 | Select | | | localhost | db1 | jack | teacher | root@localhost | 0000-00-00 00:00:00 | Select | | | localhost | db1 | tom | course | root@localhost | 0000-00-00 00:00:00 | | Select,Update | +-----------+-----+-----------+------------+----------------+---------------------+------------+---------------+ 3 rows in set (0.00 sec) mysql> revoke select on db1.teacher from 'jack'@localhost; Query OK, 0 rows affected (0.00 sec) mysql> revoke select(name,cycle),update(name,cycle) on db1.course from 'tom'@localhost; Query OK, 0 rows affected (0.00 sec)
View account information
select * from user\G
delete user
mysql> drop user jack@localhost; Query OK, 0 rows affected (0.00 sec) mysql> drop user lili@localhost; Query OK, 0 rows affected (0.00 sec)