Linux Learning Summary (45) MySQL basic operation on article

1. Log in to the database for the first time

/usr/local/mysql/bin/mysql -uroot
We found that it was directly logged in and did not need to be verified. Because we have not set a password for the root user, the default is no password. If you do not specify a user, the default is the root user login.
We found that just logging into a database and entering such a long path is too hard, and we can put that path into the environment variable.

echo "PATH=$PATH:/usr/local/msyql/bin" >> /etc/profilesource /etc/profile

Note that I made a mistake before, forgetting to write the $ sign, and the other environment variables were emptied.
The next step is to mysql-uroot the landing directly.

2 Setting the root user password

mysqlamdin -uroot password ‘xxxxxx‘
Like I set the password to Lvlinux.

We need a password for this landing.
The format is:mysql -uroot -plvlinux
Change Password
mysqladmin -uroot -p‘lvlinux‘ password ‘lvlinux1‘

3 Reset Password

If we forget the root password, we know that the root password is stored in the MySQL library, as long as we can find a way to password-free login database, you can reset or empty it.
The first step is to go to the MySQL configuration file and add the "Skip authorization" rule.
Echo ' skip-grant ' >>/etc/my.cnf
The second step, after restarting MySQL to enter the database to reset password
/etc/init.d/mysqld restart
mysql -urootLog in to Database
use mysql;Switch to MySQL Library
update user set password=password(‘lvlinux‘) where user=‘root‘;
Step Three:
Log out of MySQL, cancel the above rules, restart MySQL
sed -i ‘/skip-grant/‘d /etc/my.cnf
/etc/init.d/mysqld restart
Of course, you want to clear the password, outside the database with Mysqlamdin set password, you can use the following statement in the MySQL library.
update user set password=‘‘ where user=‘root‘;

4 connecting MySQL

Four ways to do this:
1) Local Password login
mysql -uroot -plvlinux
2) Local Socket connection
mysql -uroot -plvlinux -S/tmp/mysql.sock
-s Specifies the sockect path
3) Remote ip+ port connection
mysql -uroot -plvllinux -h192.168.226.129 -P3306
-h Specifies ip-p specified port
4) Connect and execute related statements
mysql -uroot -plvlinux -e "show databases"

5 MySQL Common commands

MySQL database structure, the database is divided into a lot of libraries, the library holds tables, table contains fields, that is, the final data information. MySQL operation, that is, for the Library, table, field operations, no outside the deletion and modification.

 查询库 show databases; 切换库 use mysql; 查看库里的表 show tables; 查看表里的字段 desc tb_name; 查看建表语句 show create table tb_name\G; 查看当前用户 select user(); 查看当前使用的数据库 select database(); 创建库 create database db1; 创建表 use db1; create table t1(`id` int(4), `name` char(40)); 查看当前数据库版本 select version(); 查看数据库状态 show status; 查看各参数 show variables; show variables like ‘max_connect%‘; 修改参数 set global max_connect_errors=1000; 查看队列 show processlist; show full processlist;

Linux Learning Summary (45) MySQL basic operation on article