51 Lessons (Set change root password, connect MySQL, MySQL common commands)

Set Change root password

Note: How to change the root password, about the root user is the MySQL Super administrator user, it is similar to the root user inside the Linux operating system, to distinguish between the root of MySQL and the system root is not a user, of course, MySQL can also create a normal user, To connect to MySQL, the default MySQL root password is empty, you can directly connect up without losing the password, so it is unsafe to set a password.

Check if MySQL is turned on before setting a password

[root@100xuni1 ~]# ps aux |grep mysql 

Start MySQL

[root@100xuni1 ~]# /etc/init.d/mysqld start

Run Mysql-uroot

[root@100xuni1 ~]# mysql -uroot


[root@100xuni1 ~]# vim /etc/profile


[root@100xuni1 ~]# source /etc/profile ##Run profile

Mysql-uroot-p to specify its password

[root@100xuni1 ~]# mysql -uroot -p

Go into MySQL to set some behavior as

Quit MySQL with quit

Set the root password

[root@100xuni1 ~]# mysqladmin -uroot password ‘hanshuo1‘ ##Set the password without hanshuo1

After you set up your password, you can't log in without an empty password

Change root password

[root@100xuni1 ~]# mysqladmin -uroot -p‘hanshuo1‘ password ‘hanshuo‘ ##密码hanshuo1 changed to hanshuo

How to change the password without knowing the root password

1. Change the configuration file

[root@100xuni1 ~]# vim /etc/my.cnf #Change the configuration file and add a skip-grant to ignore the authorization.

2. Restart the MYSQLD service to take effect

[root@100xuni1 ~]# /etc/init.d/mysqld restart

You don't need a password to log in.

3. Log in to change a library, there is a table in this library is stored password

* * Switch to MySQL library
Change the library table user This table is the password

**

Changing the user table

mysql> update user set password=password(‘hanshuo1‘) where user=‘root‘;

After setting the user table, quit quit, change the skip-grant in the/etc/my.cnf configuration file back, and then restart the mysqld

Login again with the Reset Hanshuo1.

Connect to MySQL

MySQL Common commands

Querying all libraries

Mysql> show databases; ##Query all libraries

Switch libraries

Mysql> use mysql; ##Switch library

View the tables in the library

Mysql> show tables; ##List all the tables in the library

View the fields in the table

Mysql> desc user; ##View user what fields are in this table

View the Build Table statement

Mysql> show create table user\G; ##\G is a vertical display

View Current User

mysql> select user();

Command history file

[[email protected] ~]# less .mysql_history ##Used commands are recorded under this file

View the database currently in use

mysql> select database();

Create a library

Mysql> create database db1; ##Create library db1

Add a table to the library you just created

mysql> use db1; create table t1(`id` int(4), `name` char(40));

Delete a table

mysql> drop table t1;

View Current database version

mysql> select version();

View database Status

mysql> show status;

View each parameter

mysql> show variables like ‘slow%‘;     ##View slow

modifying parameters

If you want to restart or 1000 need to be modified in the configuration file

[root@100xuni1 ~]# vim /etc/my.cnf

View queues

mysql> show processlist;

mysql> show full processlist;

Extended

mysql5.7 Root Password Change http://www.apelearn.com/bbs/thread-7289-1-1.html

Mysql5.7root has a default password, you must reset the password before you can do the MySQL operation, the following is the steps to set the root password:

First, view the default password

[root@localhost src]# cat /root/.mysql_secret

The random password set for the root Userat Fri Jan 20:00:34 (local time): ajqzsa2m

The ajqzsa2m here is the generated root random password.

Second, login MySQL

[root@localhost src]# mysql -u root -p

Enter Password:

Enter the above password ajqzsa2m login, if you do not add the path of MySQL, then use absolute path, mysql-u root-p can also be written mysql-uroot-pajqzsa2m

Third, change the password

mysql> SET PASSWORD for ' root ' @localhost = PASSWORD (' 123456 ');
Query OK, 0 rows affected (0.17 sec)

At this point, the password has been successfully modified.

MyISAM and InnoDB engine contrast http://www.pureweber.com/article/myisam-vs-innodb/
MySQL storage engine MyISAM and InnoDB's pros and cons
the answer to the https://www.zhihu.com/question/20596402

mysql configuration details: https://www.jb51.net/article/48082.htm
mysql configuration file my.cnf parameter optimization and Chinese explanation
MySQL parameter optimization for beginners, is more difficult to understand things, in fact, this parameter optimization, is a very complex thing, for different sites, and their online volume, traffic, number of posts, network conditions, as well as the machine hardware configuration has a relationship, optimization can not be completed at once, the need for constant observation and debugging , it is possible to get the best results

code show as below:

[client]
Port = 3306
Socket = /tmp/mysql.sock

[mysqld]
Port = 3306
Socket = /tmp/mysql.sock

Basedir = /usr/local/mysql
Datadir = /data/mysql
Pid-file = /data/mysql/mysql.pid
User = mysql
Bind-address = 0.0.0.0
Server-id = 1 # indicates that the serial number of the machine is 1, which is generally the meaning of the master.

Skip-name-resolve
# Disable MySQL to perform DNS resolution on external connections. Use this option to eliminate the time for MySQL to perform DNS resolution. But need to pay attention, if this option is enabled,
# Then all remote host connection authorizations must use IP address mode, otherwise MySQL will not be able to process connection requests normally.

#skip-networking

Back_log = 600
# MySQL can have the number of connections. This works when the main MySQL thread gets a lot of connection requests in a very short time.
# Then the main thread takes some time (though short) to check the connection and start a new thread. The back_log value indicates how many requests can be placed on the stack shortly before MySQL temporarily stops responding to new requests.
# If you expect to have a lot of connections in a short time, you need to increase it. In other words, if the connection data of MySQL reaches max_connections, the new request will be stored in the stack.
# To wait for a connection to release resources, the number of the stack is back_log. If the number of waiting connections exceeds back_log, the connection resources will not be granted.
# Additionally, this value (back_log) is limited to the size of the listening queue of your operating system for incoming TCP/IP connections.
# Your operating system has its own limit on this queue size (you can check your OS documentation to find the maximum value of this variable), and trying to set the back_log higher than your operating system limit will be invalid.

Max_connections = 1000
# MySQL maximum number of connections, if the server's concurrent connection request is relatively large, it is recommended to increase this value to increase the number of parallel connections, of course, this is built on the machine can support, because if the number of connections is more, between MySQL Will provide a connection buffer for each connection, it will cost more memory, so you should adjust the value appropriately, you can not blindly increase the value. You can use the ‘conn%‘wildcard to see the number of connections in the current state to determine the size of the value.

Max_connect_errors = 6000
# For the same host, if there is an interrupted error connection that exceeds the number of parameters, the host will be blocked from connecting. To unblock the host, execute: FLUSH HOST.

Open_files_limit = 65535
# MySQL open file descriptor limit, the default minimum is 1024; when open_files_limit is not configured, compare the value of max_connections*5 and ulimit -n, which one is used,
# When open_file_limit is configured, compare the values of open_files_limit and max_connections*5, which one is used.

Table_open_cache = 128
# MySQL Every time you open a table, it will read some data into the table_open_cache cache. When MySQL can't find the corresponding information in this cache, it will read it on the disk. Default 64
# Assuming the system has 200 concurrent connections, set this parameter to 200*N (N is the number of file descriptors required for each connection);
# When table_open_cache is set to be large, if the system can't handle so many file descriptors, then the client will fail and the connection will not be available.

Max_allowed_packet = 4M
# Accepted packet size; it is safe to increase the value of this variable because extra memory is allocated only when needed. For example, MySQLd allocates more memory only if you issue long queries or MySQLd must return large result rows.
# The reason why this variable takes a smaller default is a precaution to capture the error packet between the client and the server and to ensure that there is no memory overflow due to accidental use of large packets.

Binlog_cache_size = 1M
# A transaction, when not submitted, the generated log is recorded in the Cache; when the transaction commits need to be submitted, the log is persisted to disk. The default binlog_cache_size size is 32K

Max_heap_table_size = 8M
# Define the size of the memory table that the user can create. This value is used to calculate the maximum row value of the memory table. This variable supports dynamic changes

Tmp_table_size = 16M
# MySQL heap (stacked) table buffer size. All unions are done within a single DML instruction, and most unions can even be done without a temporary table.
# Most temporary tables are memory-based (HEAP) tables. Temporary tables with large record lengths (sum of lengths of all columns) or tables containing BLOB columns are stored on the hard disk.
# If an internal heap (population) table size exceeds tmp_table_size, MySQL can automatically change the in-memory heap table to the hard disk-based MyISAM table as needed. You can also increase the size of the temporary table by setting the tmp_table_size option. In other words, if you increase the value, MySQL will increase the size of the heap table, which can improve the speed of the join query.

Read_buffer_size = 2M
# MySQL reads the buffer size. A request to sequentially scan a table will allocate a read buffer, and MySQL will allocate a memory buffer for it. The read_buffer_size variable controls the size of this buffer.
# If the sequential scan request for the table is very frequent, and you think that frequent scans are going too slowly, you can increase the performance by increasing the value of the variable and the size of the memory buffer.

Read_rnd_buffer_size = 8M
# MySQL random read buffer size. When rows are read in any order (for example, in sort order), a random read buffer is allocated. When sorting queries,
# MySQL will scan the buffer first to avoid disk search and improve the query speed. If you need to sort a large amount of data, you can increase the value appropriately. But MySQL will issue this buffer for each client connection, so you should set this value as much as possible to avoid excessive memory overhead.

Sort_buffer_size = 8M
# MySQL performs the buffer size used for sorting. If you want to increase the speed of ORDER BY, first look at whether you can let MySQL use the index instead of the extra sorting stage.
# If not, try increasing the size of the sort_buffer_size variable

Join_buffer_size = 8M
# The size of the buffer that can be used by the joint query operation is the same as that of sort_buffer_size. The allocated memory corresponding to this parameter is also exclusive for each connection.

Thread_cache_size = 8
# This value (default 8) means that the number of threads stored in the cache can be reused. If there is space in the cache when disconnected, the client thread will be placed in the cache.
# If the thread is requested again, the request will be read from the cache. If the cache is empty or a new request, then the thread will be recreated. If there are many new threads,
# Increase this value to improve system performance. You can see the effect of this variable by comparing the variables in the Connections and Threads_created states. (–> indicates the value to be adjusted)
# According to the physical memory setting rules are as follows:
# 1G —> 8
# 2G —> 16
# 3G —> 32
# Greater than 3G —> 64

Query_cache_size = 8M
#MySQL's query buffer size (starting with 4.0.1, MySQL provides a query buffer mechanism) using query buffering, MySQL stores SELECT statements and query results in a buffer.
# In the future, for the same SELECT statement (case sensitive), the result will be read directly from the buffer. According to the MySQL user manual, using query buffers can achieve up to 238% efficiency.
# By checking the status value ‘Qcache_%‘, you can know if the query_cache_size setting is reasonable: if the value of Qcache_lowmem_prunes is very large, it indicates that the buffering is not enough.
# If the value of Qcache_hits is also very large, it means that the query buffer is used very frequently. In this case, you need to increase the buffer size. If the value of Qcache_hits is not large, it means that your query repetition rate is very low.
# In this case, using query buffer will affect efficiency, so you can consider not using query buffer. In addition, adding SQL_NO_CACHE to the SELECT statement can explicitly indicate that the query buffer is not used.

Query_cache_limit = 2M
#Specify the buffer size that a single query can use, default 1M

Key_buffer_size = 4M
# Specify the buffer size for the index, increase it to get a better deal of the index (for all reads and multiple rewrites), as much as you can afford. If you make it too big,
# The system will start to change pages and it really slows down. For servers with 4GB or so memory, this parameter can be set to 384M or 512M. By checking the status values Key_read_requests and Key_reads,
# You can know if the key_buffer_size setting is reasonable. The ratio key_reads/key_read_requests should be as low as possible,
# at least 1:100, 1:1000 is better (the above status values can be obtained using SHOW STATUS LIKE ‘key_read%‘). Note: The parameter value is set too large, but the overall efficiency of the server is reduced.

Ft_min_word_len = 4
#分词词汇 Minimum length, default 4

Transaction_isolation = REPEATABLE-READ
# MySQL supports 4 transaction isolation levels, they are:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
# If not specified, MySQL defaults to REPEATABLE-READ, ORACLE defaults to READ-COMMITTED

Log_bin = mysql-bin
Binlog_format = mixed
Expire_logs_days = 30 #binlog delete for more than 30 days

Log_error = /data/mysql/mysql-error.log #error log path
Slow_query_log = 1
Long_query_time = 1 #慢Query time More than 1 second is a slow query
Slow_query_log_file = /data/mysql/mysql-slow.log

Performance_schema = 0
Explicit_defaults_for_timestamp

#lower_case_table_names = 1 #not case sensitive

Skip-external-locking #MySQL option to avoid external locking. This option is enabled by default

Default-storage-engine = InnoDB #default storage engine

Innodb_file_per_table = 1
# InnoDB is a stand-alone tablespace mode, each table will generate a data space for each database
# 独立表空间 advantages:
# 1. Each table has its own separate tablespace.
# 2. The data and index of each table will exist in its own table space.
# 3. It is possible to implement a single table to move in different databases.
# 4. Space can be recycled (except for drop table operation, table space can not be recycled)
# disadvantages:
#单表增过过大, such as more than 100G
# in conclusion:
# Shared tablespace has few advantages in Insert operations. Others have no independent table space to perform well. When enabling independent tablespaces, please make reasonable adjustments: innodb_open_files

Innodb_open_files = 500
# Limit the data of the table that Innodb can open. If there are a lot of tables in the library, please increase this. This value defaults to 300

Innodb_buffer_pool_size = 64M
# InnoDB uses a buffer pool to hold indexes and raw data, unlike MyISAM.
# The bigger you set here, the less disk I/O you need to access the data in the table.
# On a standalone database server, you can set this variable to 80% of the server's physical memory size.
# Don't set too large, otherwise, the competition of physical memory may cause the page change of the operating system.
# Note that on a 32-bit system, each of your processes may be limited to a 2-3.5G user-level memory limit.
# So don't set Too high.

Innodb_write_io_threads = 4
Innodb_read_io_threads = 4
# innodb uses background threads to process read and write I/O (input and output) requests on data pages, depending on your CPU core. The default is 4
# Note: These two parameters do not support dynamic changes. You need to add this parameter to my.cnf. After the modification, restart the MySQL service. The allowed values range from 1-64.

Innodb_thread_concurrency = 0
# The default setting is 0, which means that the number of concurrent calls is not limited. The recommended setting is 0 here. It is better to play the CPU multi-core processing capability and improve the concurrency.

Innodb_purge_threads = 1
The cleanup operation in #InnoDB is a type of operation that periodically recycles useless data. In previous versions, the cleanup operation was part of the main thread, which meant that it might block other database operations at runtime.
# Starting with MySQL 5.5.X, this operation runs in a separate thread and supports more concurrent numbers. Users can choose whether to clear the operation by using the innodb_purge_threads configuration parameter.
#独螺纹, by default the parameter is set to 0 (do not use a separate thread), set to 1 means to use a separate cleanup thread. Recommended as 1

Innodb_flush_log_at_trx_commit = 2
# 0: If the value of innodb_flush_log_at_trx_commit is 0, the log buffer will be flushed to the disk every second, and no operations are performed when the transaction is committed (execution is performed by mysql's master thread thread.
# The main thread will write the redo log buffer to the redo log file (REDO LOG) of the disk every second. Regardless of whether the transaction has been committed or not) The default log file is ib_logfile0, ib_logfile1
# 1: When set to the default value of 1, each time a transaction is committed, the log buffer will be written to the log.
# 2: If set to 2, the log will be written every time the transaction is committed, but the brush will not be executed. Timing will be flushed to the log file every second. It should be noted that there is no guarantee that 100% of every second will be flushed to disk, depending on the scheduling of the process.
# Write data to the transaction log each time the transaction is committed, and the write here is only the write operation of the file system is called, and the file system is cached, so this write does not guarantee that the data has been written to Physical disk
# Default value 1 is to guarantee the full ACID. Of course, you can set this configuration item to a value other than 1 in exchange for higher performance, but in the event of a system crash, you will lose 1 second of data.
# Set to 0, when the mysqld process crashes, it will lose the last 1 second of the transaction. Set to 2, the last 1 second of data will only be lost if the operating system crashes or loses power. InnoDB ignores this value when doing recovery.
# to sum up
# Setting to 1 is of course the safest, but the performance page is the worst (relative to the other two parameters, but not acceptable). If the data consistency and integrity requirements are not high, it can be set to 2, if only the most performance, such as high concurrent write log server, set to 0 for higher performance.

Innodb_log_buffer_size = 2M
# This parameter determines the amount of memory used by some log files, in M. Larger buffers can improve performance, but unexpected failures will lose data. MySQL developers recommend setting it to between 1-8M

Innodb_log_file_size = 32M
# This parameter determines the size of the data log file. Larger settings can improve performance, but it also increases the time required to recover the failed database.

Innodb_log_files_in_group = 3
# To improve performance, MySQL can write log files to multiple files in a round-robin fashion. Recommended setting is 3

Innodb_max_dirty_pages_pct = 90
# innodb main thread refreshes the data in the cache pool, making the proportion of dirty data less than 90%

Innodb_lock_wait_timeout = 120
# InnoDB A transaction can wait for a locked timeout seconds before being rolled back. InnoDB automatically detects transaction deadlocks and rolls back transactions in its own lock table. InnoDB noticed the lock settings with the LOCK TABLES statement. The default is 50 seconds

Bulk_insert_buffer_size = 8M
# Bulk insert cache size, this parameter is for the MyISAM storage engine. It is suitable for improving efficiency when inserting 100-1000+ records at a time. The default is 8M. It can be doubled for the amount of data.

Myisam_sort_buffer_size = 8M
# MyISAM sets the size of the buffer used when restoring the table, sorting the buffer allocated by MyISAM index when creating an index or ALTER TABLE in REPAIR TABLE or CREATE INDEX

Myisam_max_sort_file_size = 10G
# If the temporary file becomes more than indexed, do not use the quick sort index method to create an index. Note: This parameter is given in bytes

Myisam_repair_threads = 1
# If the value is greater than 1, create a MyISAM table index in parallel during the Repair by sorting process (each index is in its own thread)

Interactive_timeout = 28800
# The number of seconds the server waits for activity before closing the interactive connection. The interactive client is defined as a client that uses the CLIENT_INTERACTIVE option in mysql_real_connect(). Default: 28800 seconds (8 hours)

Wait_timeout = 28800
# The number of seconds the server waits for activity before closing a non-interactive connection. When the thread starts, the session wait_timeout value is initialized according to the global wait_timeout value or the global interactive_timeout value.
# depends on the client type (defined by the connection option CLIENT_INTERACTIVE of mysql_real_connect()). Parameter default value: 28800 seconds (8 hours)
# The maximum number of connections supported by the MySQL server is capped, because the establishment of each connection consumes memory, so we want the client to connect to MySQL Server after processing the corresponding operation.
# Should disconnect and release the occupied memory. If your MySQL Server has a large number of idle connections, they will not only consume memory in vain, but if the connection has been accumulated and not broken,
# Ultimately will reach the maximum number of connections to MySQL Server, which will report ‘too many connections’ error. For the value setting of wait_timeout, it should be judged according to the operation of the system.
# After the system is running for a period of time, you can use the show processlist command to check the connection status of the current system. If a large number of sleep processes are found, the parameter is set too large.
# Can make appropriate adjustments to be smaller. To set both interactive_timeout and wait_timeout to take effect.

[mysqldump]
Quick
Max_allowed_packet = 16M #Maximum packet length sent and accepted by the server

[myisamchk]
Key_buffer_size = 8M
Sort_buffer_size = 8M
Read_buffer = 4M
Write_buffer = 4M  

MySQL Tuning: http://www.aminglinux.com/bbs/thread-5758-1-1.html

MySQL tuning can be done in several ways:
1. Architecture Layer:
Do from the library, to achieve read and write separation;

2. System level:
Increased memory;
RAID0 or RAID5 the disk to increase the read and write speed of the disk;
You can re-mount the disk and add the Noatime parameter, which reduces the I/O to the disk;

3. mysql itself tuning:
(1) If master-slave synchronization is not configured, the Bin-log function can be turned off to reduce disk I/O
(2) Add skip-name-resolve to my.cnf to avoid slow MySQL execution due to delayed parsing of host names
(3) Adjust several key buffer and cache. The basis of adjustment, mainly according to the state of the database debugging. You can refer to 5 for tuning.

4. Application level:
View the slow query log, based on the SQL statements in the slow query log optimizer, such as increasing the index

5. Adjust several key buffer and cache

1) Key_buffer_size first can be based on the system's memory size set it, probably a reference value: 1G or less memory settings 128m;2g/256m; 4g/384m;8g/1024m;16g/2048m. This value can be checked by checking the status values key_read_requests and key_reads to see if the key_buffer_size settings are reasonable. The proportional key_reads/key_read_requests should be as low as possible, at least 1:100,1:1000 better (the above status values can be obtained using the show status like ' key_read% '). Note: This parameter value setting is too large to be the overall efficiency of the server down!

2) Table_open_cache open a table, will temporarily put the data inside the table in this part of memory, generally set to 1024 is enough, its size we can measure by this way: if you find Open_tables equals Table_cache, And Opened_tables is growing, you need to increase the value of Table_cache (the above status values can be obtained using show status like ' Open%tables '). Note that you cannot blindly set the Table_cache to a very large value. If set too high, it may cause insufficient file descriptors, resulting in performance instability or connection failures.

3) Sort_buffer_size the size of the buffer that can be used when the query is sorted, the allocated memory for that parameter is per-connection exclusive! If there are 100 connections, the actual allocated total sort buffer size is 100x4 = 400MB. Therefore, the recommended setting for a server that has around 4GB is 4-8m.

4) read_buffer_size The buffer size that can be used by the read query operation. As with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection!

5) join_buffer_size The buffer size that can be used by the Federated query operation, as with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection!

6) Myisam_sort_buffer_size This buffer is primarily used to fix the memory used by the sort index during the table process or the memory size used to sort the index when indexing, generally 4G memory to 64M.

7) query_cache_size MySQL query operation buffer size, adjusted by the following practices: SHOW STATUS like ' qcache% '; If qcache_lowmem_prunes this parameter records how many queries are removed from the query cache because of insufficient memory. With this value, the user can adjust the cache size appropriately. If the value is very large, it indicates that buffering is often insufficient, and the cache size needs to be increased; Qcache_free_memory: Query cache memory size, through this parameter can be very clear know the current system query memory is sufficient, is more, or not enough, we can make adjustments according to the actual situation. In general, 4G memory setting 64M is sufficient.

8) thread_cache_size indicates that the number of threads stored in the cache can be re-used, referring to the following values:1g-> 8 2g-> 3g-> >3G-> 64
In addition, there are a few more key parameters:

9) Thread_concurrency This value is set to twice times the number of CPU cores

Wait_timeout indicates idle connection timeout time, default is 28800s, this parameter is used with Interactive_timeout, that is, for wait_timeout to take effect, you must set the Interactive_ Timeout, it is recommended that both of them are set to 10

One) max_connect_errors is a security-related counter value in MySQL that is responsible for blocking excessive attempts by clients that fail to prevent brute-force password violations. is not much related to performance. In order to avoid some errors, we generally set a larger, for example, 10000

Max_connections Maximum number of connections, according to the amount of business request to adjust appropriately, set 500 enough

Max_user_connections refers to the maximum number of connections that the same account can connect to the MySQL service at the same time. Set to 0 to indicate no limit. Usually we set it to 100 enough

Personal MySQL tuning experience shared by classmates: http://www.apelearn.com/bbs/thread-11281-1-1.html

51 Lessons (Set change root password, connect MySQL, MySQL common commands)