2018-05-07 Linux Learning

13.1 Setting Change root password

/usr/local/mysql/bin/mysql-uroot
Change environment variable path, add MySQL absolute path

Mysqladmin-uroot password ' 123456 '
mysql-uroot-p123456

Password reset
VI/ETC/MY.CNF//Add Skip-grant
Restart MySQL service/etc/init.d/mysqld restart
Mysql-uroot
Use MySQL;
Update user set Password=password (' Aminglinux ') where user= ' root ';

Operation Process

Add path
[Email protected] ~]# Mysql-uroot
-bash:mysql: Command not found
[Email protected] ~]# Ls/usr/local/mysql/bin/mysql
/usr/local/mysql/bin/mysql

[[email protected] ~]# echo $PATH/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin[[email protected] ~]# export PATH=$PATH:/usr/local/mysql/bin/[[email protected] ~]# echo $PATH/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin/[[email protected] ~]# mysql -urootWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.6.39 MySQL Community Server (GPL)Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql>

Set Password

[[email protected] ~]# mysqladmin -uroot password ‘aminglinux.1‘Warning: Using a password on the command line interface can be insecure.[[email protected] ~]# mysql -uroot -pEnter password: 输入密码能正常登陆

Change Password

[[email protected] ~]# mysqladmin -uroot -p‘aminglinux.1‘ password ‘aminglinux.2‘Warning: Using a password on the command line interface can be insecure.[[email protected] ~]# mysql -uroot -pEnter password: 输入密码能正常登陆

Reset Password (password not known)

[[email protected] ~]# vim /etc/my.cnf[mysqld]skip-grantdatadir=/data/mysqlsocket=/tmp/mysql.sock[[email protected] ~]# /etc/init.d/mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! [[email protected] ~]# mysql -urootmysql> use mysqlmysql> select * from user;mysql> update user set password=password(‘aminglinux‘) where user=‘root‘;[[email protected] ~]# vim /etc/my.cnf[mysqld]datadir=/data/mysqlsocket=/tmp/mysql.sock[[email protected] ~]# /etc/init.d/mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS![[email protected] ~]# mysql -uroot -pEnter password: 输入密码能正常登陆

13.2 Connecting MySQL

mysql-uroot-p123456
mysql-uroot-p123456-h127.0.0.1-p3306
Mysql-uroot-p123456-s/tmp/mysql.sock
Mysql-uroot-p123456-e "Show Databases"

[[email protected] ~]# mysql -uroot -paminglinux -e "show databases"Warning: Using a password on the command line interface can be insecure.+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || test               |+--------------------+

13.3 MySQL Common commands

Query library show databases;
Switch the library use MySQL;
View the table in the library show tables;
View the fields in the table desc tb_name;
View Build Table statement Show create TABLE tb_name\g;
View the current user Select User ();
View the database you are currently using Select Database ();

Creating a library Create database db1;
CREATE table use DB1; CREATE TABLE T1 ( id int (4), name char (40));
View current database version select version ();
View database status Show status;
View each parameter show variables; Show variables like ' max_connect% ';
Modify parameter set global max_connect_errors=1000;
View queue show Processlist; Show full processlist;

Operation Process

Mysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | | Test |+--------------------+4 rows in Set (0.00 sec) mysql> use MySQL; Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W Ith-adatabase changedmysql> show tables;+---------------------------+| Tables_in_mysql |+---------------------------+| Columns_priv | | db | | Event | | Func | | General_log | | Help_category | | Help_keyword | | help_relation | | Help_topic | | Innodb_index_stats | | Innodb_table_stats | | Ndb_binlog_index | | Plugin | | Proc | | Procs_priv | | Proxies_priv | | Servers | | Slave_mAster_info | | Slave_relay_log_info | | Slave_worker_info | | Slow_log | | Tables_priv | | Time_zone | | Time_zone_leap_second | | Time_zone_name | | time_zone_transition | | Time_zone_transition_type | |  User |+---------------------------+28 rows in Set (0.00 sec) mysql> desc user;mysql> Show Create Table user\g;mysql> Select User (); +----------------+| User () |+----------------+| [email protected] |+----------------+1 row in Set (0.00 sec)

Will resolve the login IP

[[email protected] ~]# mysql -uroot -paminglinux -h192.168.106.160mysql> select user();+---------------+| user()        |+---------------+| [email protected] |+---------------+1 row in set (0.00 sec)

View Gallery

mysql> select database();+------------+| database() |+------------+| NULL       |+------------+1 row in set (0.00 sec)mysql> use mysqlReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select database();+------------+| database() |+------------+| mysql      |+------------+1 row in set (0.00 sec)

Create a library

mysql> create database db1;Query OK, 1 row affected (0.00 sec)mysql> use db1;Database changed

Create a table

mysql> create table t1(`id` int(4), `name` char(40));Query OK, 0 rows affected (0.01 sec)mysql> show create table t1\G;*************************** 1. row ***************************       Table: t1Create Table: CREATE TABLE `t1` (  `id` int(4) DEFAULT NULL,  `name` char(40) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.01 sec)ERROR: No query specified

Create TABLE, specify string

mysql> create table t1(`id` int(4), `name` char(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8;                                                                        Query OK, 0 rows affected (0.01 sec)mysql> show create table t1\G;*************************** 1. row ***************************       Table: t1Create Table: CREATE TABLE `t1` (  `id` int(4) DEFAULT NULL,  `name` char(40) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)ERROR: No query specified

View version

mysql> select version();+-----------+| version() |+-----------+| 5.6.39    |+-----------+1 row in set (0.00 sec)

View database Status

mysql> show status;+-----------------------------------------------+-------------+| Variable_name                                 | Value       |+-----------------------------------------------+-------------+| Aborted_clients                               | 0           || Aborted_connects                              | 0           || Binlog_cache_disk_use                         | 0           || Binlog_cache_use                              | 0           |

View each parameter

mysql> show variables;mysql> show variables like ‘max_connect%‘;+--------------------+-------+| Variable_name      | Value |+--------------------+-------+| max_connect_errors | 100   || max_connections    | 151   |+--------------------+-------+2 rows in set (0.00 sec)mysql> show variables like ‘slow%‘;+---------------------+-------------------------------+| Variable_name       | Value                         |+---------------------+-------------------------------+| slow_launch_time    | 2                             || slow_query_log      | OFF                           || slow_query_log_file | /data/mysql/linux-01-slow.log |+---------------------+-------------------------------+3 rows in set (0.00 sec)

Modify parameters, temporary effect, if long-term effective need to change the configuration file my.conf

mysql> set global max_connect_errors=1000;Query OK, 0 rows affected (0.01 sec)mysql> show variables like ‘max_connect%‘;+--------------------+-------+| Variable_name      | Value |+--------------------+-------+| max_connect_errors | 1000  || max_connections    | 151   |+--------------------+-------+2 rows in set (0.01 sec)

View queues

mysql> show processlist;+----+------+-----------+------+---------+------+-------+------------------+| Id | User | Host      | db   | Command | Time | State | Info             |+----+------+-----------+------+---------+------+-------+------------------+| 10 | root | localhost | db1  | Query   |    0 | init  | show processlist |+----+------+-----------+------+---------+------+-------+------------------+1 row in set (0.00 sec)mysql> show full processlist;+----+------+-----------+------+---------+------+-------+-----------------------+| Id | User | Host      | db   | Command | Time | State | Info                  |+----+------+-----------+------+---------+------+-------+-----------------------+| 10 | root | localhost | db1  | Query   |    0 | init  | show full processlist |+----+------+-----------+------+---------+------+-------+-----------------------+1 row in set (0.00 sec)

2018-05-07 Linux Learning