Shulou(Shulou.com)05/31 Report--

What are the MySQL operating specifications, I believe many inexperienced people do not know what to do about this, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

User Rights Management create user

Command: CREATE USER 'username'@'host' IDENTIFIED BY' password'

Description:

The user name created by Username

Host specifies the host on which the user can log in. If the localhost is available for local users, if you want the user to log in from any remote host, you can use the wildcard%.

Password the user's login password, the password can be empty, if it is empty, the user can log on to the server without a password.

Such as: CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY' 123456'

Note: when creating users, you can also add user information to the user table to add users.

Authority assignment

MySQL permission principles:

Permission control is mainly for security reasons, so you need to follow the following empirical principles:

1. Grant only the minimum permissions that can meet the needs to prevent users from overstepping their authority. If the user only needs to query, then only give select permission, do not give the user update, insert or delete permission.

2. Limit the login host of a user when creating a user, which is generally limited to a specified IP or private network IP segment.

3. Delete users without passwords when initializing the database. When the database is installed, some users are automatically created who do not have a password by default.

4. Set a password that meets the password complexity for each user.

5. Clean up unwanted users regularly. Reclaim permissions or delete users.

MYSQL permission list:

Authority

Permission level

Permission description

CREATE

Database, table, or index

Create database, table, or index permissions

DROP

Database or table

Delete database or table permissions

GRANT OPTION

A database, table, or saved program

Give permission option

REFERENCES

Database or table

ALTER

Table

Change tables, such as adding fields, indexes, etc.

DELETE

Table

Delete data permissions

INDEX

Table

Index permission

INSERT

Table

Insert permission

SELECT

Table

Query permission

UPDATE

Table

Update permissions

CREATE VIEW

View

Create view permissions

SHOW VIEW

View

View view permissions

ALTER ROUTINE

Stored procedure

Change stored procedure permissions

CREATE ROUTINE

Stored procedure

Create stored procedure permissions

EXECUTE

Stored procedure

Execute stored procedure permissions

FILE

File access on the server host

File access permission

CREATE TEMPORARY TABLES

Server management

Create temporary table permissions

LOCK TABLES

Server management

Lock table permission

CREATE USER

Server management

Create user permissions

PROCESS

Server management

View process permissions

RELOAD

Server management

Permission to execute flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload, etc.

REPLICATION CLIENT

Server management

Copy permission

REPLICATION SLAVE

Server management

Copy permission

SHOW DATABASES

Server management

View database permissions

SHUTDOWN

Server management

Turn off database permissions

SUPER

Server management

Execute kill thread permissions

How the permissions of MYSQL are distributed, that is, what permissions can be set for tables and columns, and so on, can be explained from a table in the official document:

Permission distribution

Permissions for possible settings

Table permissions

'Select', 'Insert',' Update', 'Delete',' Create', 'Drop',' Grant', 'References',' Index', 'Alter'

Column permissions

'Select', 'Insert',' Update', 'References'

Process permission

'Execute', 'Alter Routine',' Grant'

MySQL permission control:

1. Instructions for using the GRANT command:

Create a superuser fog that is only allowed to log in locally, and allow permissions to be granted to other users with the password: test123

Mysql > grant all privileges on *. * to fog@'localhost' identified by "test123" with grant option

The GRANT command describes:

ALL PRIVILEGES means all permissions, and you can also use select, update and other permissions.

ON is used to specify which libraries and tables the permissions are for.

The preceding * sign in *. * is used to specify the database name, and the subsequent * sign is used to specify the table name.

TO means to grant permissions to a user.

Fog@'localhost' stands for fog user, @ followed by restricted host, can be IP, IP segment, domain name and%,% means anywhere. Note: some versions here do not include local. I have previously set% for a user to log in anywhere, but not locally. This has something to do with the version. If you encounter this problem, add a localhost user.

IDENTIFIED BY specifies the user's login password.

The WITH GRANT OPTION option means that the user can delegate the permissions he or she has to others.

Note: there are often people who create an operating user without specifying the WITH GRANT OPTION option so that the user cannot later use the GRANT command to create a user or authorize another user.

2. Refresh permissions

Use this command to make permissions effective, especially if you make update or delete updates to those permission tables such as user, db, host, etc. In the past, when the permissions are not updated after using grant, use the FLUSH PRIVILEGES command to refresh the permissions as long as you make changes to the permissions.

Mysql > flush privileges

3. View permissions

View the permissions of the current user:

Mysql > show grants

+-- +

| | Grants for root@localhost |

+-- +

| | GRANT ALL PRIVILEGES ON *. * TO 'root'@'localhost' WITH GRANT OPTION |

| | GRANT PROXY ON'@''TO 'root'@'localhost' WITH GRANT OPTION |

+-- +

View the permissions of a user:

Mysql > show grants for 'fog'@'%'

4. Reclaim permissions

Mysql > revoke delete on *. * from 'fog'@'localhost'

5. Delete a user

Mysql > drop user 'fog'@'localhost'

Query OK, 0 rows affected (0.01 sec)

6. Rename the account

Mysql > rename user 'fog'@'%' to' jim'@'%'

7. Change the password

1. Use the set password command

Mysql > SET PASSWORD FOR 'root'@'localhost' = PASSWORD (' 123456')

2. Use mysqladmin

[root@rhel5] # mysqladmin-uroot-p123456 password 1234abcd

Format: mysqladmin-u username-p old password password new password

3. Edit the user table directly with update

Mysql > use mysql

Mysql > update user set PASSWORD = PASSWORD ('1234abcd') where user =' root'

Mysql > flush privileges

SQL development recommendations about naming

1. Library names, table names and field names must be in lowercase letters and separated by underscores.

2. The maximum length of library name, table name and field name is 64 characters, and the maximum length is no more than 32 characters.

3. MySQL reserved words are prohibited for library names, table names and field names.

When the library name, table name, field name and other attributes contain reserved words, the SQL statement must refer to the attribute name in reverse quotation marks, which will make the writing of SQL statements and the escape of variables in SHELL scripts very complicated.

4. Try not to use partition tables.

Partitioned tables have strict requirements for partitioning keys; after partitioned tables become larger, it becomes more difficult to perform DDL, SHARDING, single table recovery, and so on. Therefore, the use of partition tables is prohibited, and the business side is advised to manually SHARDING.

About creating tabl

1. Use INNODB storage engine.

INNODB engine is the default lead power after MySQL5.5 version, which supports transaction and row-level locks, better data recovery, better concurrency performance, better support for hardware such as multi-core, large memory, SSD, hot backup of data, etc., so INNODB has obvious advantages over MyISAM.

2. It is recommended to use UNSIGNED to store non-negative values.

With the same number of bytes, the range of values for non-negative storage is larger. For example, the signed TINYINT is-128127and the unsigned is 0255.

3. It is recommended to use INT UNSIGNED to store IPV4.

UNSINGED INT stores 4 bytes of IP addresses, while CHAR (15) takes 15 bytes. In addition, the computer processes integer types faster than string types. Use INT UNSIGNED instead of CHAR (15) to store IPV4 addresses and convert them through the MySQL functions inet_ntoa and inet_aton. There is currently no conversion function for IPv6 addresses, and you need to use DECIMAL or two BIGINT to store them.

For example:

SELECT INET_ATON ('209.207.224.40'); 3520061480

SELECT INET_NTOA (3520061480); 209.207.224.40

4. It is strongly recommended to use TINYINT instead of the ENUM type.

The ENUM type requires online DDL when you need to modify or add enumerated values, which is expensive; if the ENUM column value contains a numeric type, it may cause confusion of default values.

Use VARBINARY to store case-sensitive variable-length strings or binary content.

VARBINARY is case-sensitive by default, has no concept of character set, and is fast.

6. The INT type always takes up 4 bytes of storage. For example, INT (4) only represents the display character width of 4 bits, not the storage length.

The number after the parenthesis of the numerical type only indicates the width and has nothing to do with the storage range. For example, INT (3) displays 3 digits by default, blanks are filled, and displays normally when it is exceeded. Python, java clients do not have this function.

7. Distinguish between DATETIME and TIMESTAMP. The storage year uses the YEAR type. The storage date uses the DATE type. The TIMESTAMP type is recommended for storage time (accurate to seconds).

Both DATETIME and TIMESTAMP are accurate to seconds, so TIMESTAMP is preferred because TIMESTAMP has only 4 bytes and DATETIME has 8 bytes. At the same time, TIMESTAMP has the characteristics of automatic assignment and automatic update.

Note: in versions 5.5 and earlier, if there were more than one timestamp column in a table, at most one column could have automatic updates.

8. Split the fields with large fields and low access frequency into separate tables for storage, and separate hot and cold data.

It helps to make effective use of the cache, prevent reading useless cold data, reduce disk IO, and ensure that hot data resides in memory to improve cache hit rate.

9. It is forbidden to store plaintext passwords in database tables.

The encrypted string is used to store the password, and the password can not be decrypted, and the random string is added with salt to ensure the security of the password. To prevent database data from being obtained by internal personnel or hackers, use dictionary attacks and other means to violently crack user passwords.

15. The table must have a primary key, and it is recommended to use the UNSIGNED self-incrementing column as the primary key.

Tables do not have primary keys, and INNODB sets hidden primary key columns by default; tables without primary keys are very difficult to locate data rows and reduce the efficiency of row-based replication.

10. Use UTF8 for the table character set, and apply for the UTF8MB4 character set if necessary.

A) the UTF8 character set takes 3 bytes to store Chinese characters and one byte to store English characters.

B) UTF8 is unified and universal, and there is no risk of garbled code in transcoding.

C) if you encounter the storage needs of emoticons such as EMOJ, you can apply to use the UTF8MB4 character set.

11. Adopt the appropriate strategy of database and table. For example, ten thousand libraries and ten tables, ten hundred tables and so on.

The adoption of appropriate sub-database and sub-table strategy is conducive to the rapid horizontal split of the database in the later stage of business development. at the same time, the sub-database can effectively make use of the multi-thread replication characteristics of MySQL.

About Index

1. Redundant indexes are prohibited.

Index is a double-edged sword, which will increase the maintenance burden and IO pressure. (a), (b), (b), the latter is a redundant index. Prefix indexing can be used to achieve acceleration and reduce the maintenance burden.

2. Duplicate indexing is prohibited.

Primary key a share Uniq index a; duplicate indexes increase the maintenance burden and take up disk space without any benefit.

3. Do not index on low cardinality columns, such as "gender".

In most scenarios, the precise lookup of indexing on low cardinality columns has no advantage over full table scans without indexing, and increases the burden on IO.

4. Use the overlay index reasonably to reduce IO and avoid sorting.

The overlay index can get all the required fields from the index, so as to avoid secondary lookup back to the table and save IO. In the INNODB storage engine, secondary index (non-primary key index, also known as secondary index, secondary index) does not directly store row addresses, but primary key values. If users need to query data columns that are not included in secondary index, they need to find the primary key value first through secondary index, and then query other data columns through the primary key, so they need to query twice. The overlay index can get all the needed data in one index, so it is more efficient. Primary key queries are natural override indexes. For example, SELECT email,uid FROM user_email WHERE uid=xx, if uid is not the primary key, you can add the index as index (uid,email) in due course to achieve a performance improvement.

About SQL

1. Replace OR with IN. The IN in the SQL statement should not contain too many values and should be less than 1000.

IN is a range lookup, and MySQL will sort the list values of IN and look up them, which is more efficient than OR.

2. Replace UNION with UNION ALL. UNION ALL does not need to sort the result set again.

3. Try not to use order by rand ().

Order by rand () adds a pseudo column to the table, then uses the rand () function to calculate the rand () value for each row of data, and then sorts based on that row, which usually results in a temporary table on disk, so it is very inefficient. It is recommended that you first use the rand () function to get the random primary key value, and then get the data through the primary key.

4. It is suggested that a reasonable paging method should be used to improve the paging efficiency.

5. SELECT only gets the necessary fields and uses SELECT * as little as possible.

6. Functions in SQL to avoid uncertain results such as now (), rand (), sysdate (), current_user (), etc.

In the scenario of statement-level replication, the master-slave data is inconsistent, and the SQL statement generated by the function of uncertain value cannot make use of QUERY CACHE.

7. Reduce the number of interactions with the database and use batch SQL statements as much as possible.

Use the following statement to reduce the number of interactions with db:

A) INSERT... ON DUPLICATE KEY UPDATE

B) REPLACE INTO

C) INSERT IGNORE

D) INSERT INTO VALUES ()

8. Split the complex SQL into multiple small SQL to avoid large transactions.

Simple SQL makes it easy to use MySQL's QUERY CACHE; to reduce table locking time, especially when MyISAM; can use multicore CPU.

9. Multiple alter operations on the same table must be merged into one operation.

10. Minimize the use of stored procedures, triggers, views, custom functions, etc.

These advanced features have performance problems, as well as more unknown BUG. Putting the business logic into the database will make the database DDL, SCALE OUT, SHARDING and so on more difficult.

Other

1. The application account that controls super permissions exists.

2. For the requirements of table modification on the submission line, all SQL statements (including INSERT, DELETE, UPDATE) involved must be specified in detail to facilitate DBA audit and optimization.

It's not just SELECT statements that need to use indexes. Both UPDATE and DELETE need to locate the data before making changes. Therefore, the business is required to provide all the SQL statements to facilitate DBA audit.

3. Do not store business logic in MySQL database.

The database is a stateful service, and the change is complex and slow. if the business logic is put into the database, it will limit the rapid development of the business. It is suggested that the business logic should be put on the front-end or middle logic layer in advance, and the database should be used as the storage layer to realize the separation of logic and storage.

After reading the above, have you mastered the methods of MySQL operating specifications? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.