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

Notes:

13.1 Settings Change root password

13.2 connect to MySQL

13.3 mysql common command

Date of note: October 30, 2017

13.1 Settings Change root password

Root is the most privileged user of mysql, just like Linux root. By default, mysql's root password is empty and you can log in directly. But it's not safe, so you have to set a password.

If the mysql command is not added to PATH, you must add it first before you can use the mysql command directly, otherwise you must use the absolute path, the command is as follows:

export PATH=$PATH:/usr/local/mysql/bin/

Then use mysql -uroot command, you can log in to mysql directly:

To make this environment variable permanent, you need to configure it in your profile:

vim /etc/profile

Then reload this file:

source /etc/profile

mysql-p is the specified password, but there is no password yet, so just enter:

Use exit or quit to exit mysql.

Set the password using the following command:

mysqladmin -uroot password '123456'

After setting the password, you cannot log in directly using mysql -uroot:

Then we use mysql -uroot -p to specify the password:

The following way to change the root password needs to know the original password to change, otherwise it cannot be changed, and it is also changed by using the mysqladmin command, as follows:

Now you need to use the changed password to log in to mysql:

If you don't know root's password, or have forgotten it, there is another way to reset it. First edit the my.cnf configuration file and add the following under [mysqld]:

This sentence is used to skip passwords and ignore passwords.

After modification, restart the service:

service mysqld restart

After restarting, you can log in directly by using mysql -uroot:

After logging in, we need to change the password through a table, first use mysql; select mysql library:

Then use desc user; to view the table structure of the user table, where you can see the fields for User and Password:

What we are going to modify now is the Password field. The sql statement is as follows:

update user set password=password('12345') where user='root';

After modifying, exit mysql, and then comment out the sentence skipping the password in my.cnf:

vim /etc/my.cnf

Then restart mysql, then login will need to use your changed password:

13.2 connect to MySQL

Here are a few common commands to connect to MySQL:

mysql -uroot -p12345

This command, is more commonly used, connect the mysql command of the local machine, just used this command to connect mysql, in this will not repeat.

mysql -uroot -p12345 -h227.0.0.1 -P3306

This command is to connect to remote mysql, for example, machine A to connect to mysql of machine B, you need to use this command, example:

mysql -uroot -p12345 -S/tmp/mysql.sock

This command is connected to mysql through sock. There is a communication method used in Linux/Unix operating systems, which is sock, but this method can only be used locally, so it is actually the same as the first command:

mysql -uroot -p12345 -e "show databases"

This command executes a sql statement at login time through the-e option. This sql statement is used to list all databases in mysql. This situation is generally used in shell scripts:

13.3 mysql common command

mysql commands need to be logged into mysql to execute, so before this, I want to introduce how to connect mysql. Since I know how to connect mysql, I'll start using mysql's common commands:

query library show databases; this command was also used earlier:

Switch library use mysql; this command switches to mysql library:

View all the tables in the library show tables;

Look at the fields desc tb_name in the table; the library contains the table, and the table contains the fields:

Check the table creation statement show create table tb_name\G; if you don't add G, it will appear very messy:

Select user(); this user() is a function:

If you log in remotely, root@ hostname is displayed here, root@localhost is displayed for local login

The mysql command history is recorded in the.mysql_history file in the root directory:

select database();

Now no database is selected, so null is displayed. To select a database, the name of the current database will be displayed:

create database db1;

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

At this point we can see the table creation statement using the show create table t1\G; statement, followed by the default engine and default character set:

If you don't want to use this default character set, you can specify another character set when creating the table, for example:

Select version();

View database status show status;

Looking at the parameters show variables; lists a lot of things:

For example, I want to see the max_connect_errors parameter:

show variables like 'max_connect_errors';

If you want to see a parameter but can't remember the full name, you can use a fuzzy query:

show variables like 'max_connect%';

modify parameter set global max_connect_errors=1000;

It needs to be permanently effective. It needs to be modified in my.cnf.

viewing queues show processlist; viewing queues is the same as using ps or top commands to view system health on Linux:

View full queue show full processlist;

extended

mysql5.7 root password change

http://www.apelearn.com/bbs/thread-7289-1-1.html

Myisam vs innodb engine comparison

http://www.pureweber.com/article/myisam-vs-innodb/

MySQL configuration details:

http://blog.linuxeye.com/379.html

mysql tuning:

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

Students share their own mysql tuning experience:

http://www.apelearn.com/bbs/thread-11281-1-1.html

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.