Mysql DBA Advanced Operations Learning Note-mysql Common Basic command combat

7.1 Single-instance MySQL startup and shutdown methods

(1) General method to start the database

1. Start the MySQL service command

[[email protected] ~]# /etc/init.d/mysqld startStarting MySQL. SUCCESS!

2. View MySQL Port

[[email protected] ~]# ss -lnt|grep 3306LISTEN 0  

3. View the MySQL process

will start two processes the first one is Mysql_safe and the second one is mysqld.

[[email protected] ~]# ps -ef|grep mysql|grep -v greproot   2796  1  0 16:23 pts/000:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/var --pid-file=/usr/local/mysql/var/localhost.localdomain.pidmysql  2912   2796  

4.MySQL Database Startup principle

/etc/init.d/mysqld is a shell startup script that eventually invokes the Mysqld_safe script and finally calls the Mysqld service to start MySQL.

as follows, the Mysqld_safe program is called in the/etc/init.d/mysqld script.

$bindir/mysqld_safe --datadir=$datadir --pid-file=$server_pid_file $other_args >/dev/null 2>&1 &

(2) How to start the MySQL system output when initializing the database

Mysqld_safe--user=mysql &

Tips:

1. When the root password is retrieved, it is often started with mysqld_safe–user=mysql & with parameters

2. This method is used when we develop our own database startup scripts.

3./etc/init.d/mysqld and Mysqld_safe--user=mysql are essentially the same as the start. Generally when the fault we use Mysqld_safe to start, because you can add parameters.

[[email protected] ~]# cd /usr/local/mysql/|mysqld_safe &

(3) General method to close the database

1. Turn off the MySQL command

[[email protected] ~]# /etc/init.d/mysqld stopShutting down MySQL. SUCCESS![[email protected] ~]# ss -lnt|grep 3306

2.MySQL General Shutdown Database principle

‘stop‘)# Stop daemon. We use a signal here to avoid having to know the# root password.# The RedHat / SuSE lock directory to removelock_dir=/var/lock/subsys/mysqlmanager# If the manager pid_file doesn‘t exist, try the server‘sif test ! -s "$pid_file"then  pid_file=$server_pid_file  lock_dir=/var/lock/subsys/mysqlfiif test -s "$pid_file"then  mysqlmanager_pid=`cat $pid_file`  if (kill -0 $mysqlmanager_pid 2>/dev/null)  thenecho $echo_n "Shutting down MySQL"kill $mysqlmanager_pid# mysqlmanager should remove the pid_file when it exits, so wait for it.wait_for_pid removed "$mysqlmanager_pid"; return_value=$?  elselog_failure_msg "MySQL manager or server process #$mysqlmanager_pid is not running!"rm $pid_file  fi

(4) Forced shutdown of MySQL

Killall mysqld

Pkill mysqld

Killall-9 mysqld

Kill PID

Tip: The second method starts and shuts down the database general production environment is not used, especially the close command.

Emphasis: Try not to Savage kill MySQL service, production high concurrency environment may cause data loss.

The following refers to the old boy teacher's blog post, brutal killing database caused by the failure of the Enterprise case:

http://oldboy.blog.51cto.com/2561410/1431161

http://oldboy.blog.51cto.com/2561410/1431172

Http://www.cnblogs.com/peida/archive/2012/12/20/2825837.html

(5) Graceful Close database method

The first method of Mysqladmin

[Email protected] ~]# mysqladmin-uroot-p123456 shutdown

The second method comes with a script
/etc/init.d/mysqld stop

7.1.1 multi-instance MySQL startup and shutdown method instance

Start:

/data/3306/mysql start/data/3307/mysql start

Shut down:

/data/3306/mysql stop/data/3306/mysql stop

Multi-instance startup script implementation startup and shutdown methods

Start:

${cmdPath}/mysqld_safe --defaults-file=${myPath}/my.cnf --user=mysql --basedir=${softPath} --datadir=${myPath}/data &>/dev/null &

Shut down:

mysqladmin -u"${my_user}" -p"${my_pass}" -S "$socketfile" shutdown

7.2 Log on to the MySQL method

7.2.1 single-instance MySQL login

①mysql just finished loading the database without password login, no password.

②mysql–uroot just finished loading the database without password login, no password.

③mysql–uroot–p This is the standard command-line logon command.

④mysql–uroot–p ' 123456 ' non-scripting is generally not used, password plaintext will reveal the password can disguise the history of the function of resolution.

Workaround:

History–c emptying history

History–d Delete a specified line

You can view the system history to delete

[[email protected] 3306]# cat /root/.bash_history

You can also view the MySQL history to delete

[[email protected] 3306]# cat /root/.mysql_history

The default prompt after login is mysql> this prompt can be modified to differentiate between the test environment and the formal environment. Be sure to differentiate between formal and test environments. Both the formal environment and the test environment should be backed up in action first.
To change the MySQL data logon prompt (learn the knowledge) the following method:

1. Command-line modification prompt

mysql> prompt \[email protected] \r:\m\s-> 这种改变是临时的不生效的

2. configuration file modification prompt

In the MY.CNF configuration file under the [MySQL] module to add the following, save, no need to restart MySQL, quit the current session to log back in, if added in the configuration file, you can use \ Avoid the problem of escaping.

[mysql]no-auto-rehashprompt \\[email protected] \\r:\\m\\s->

7.2.2 multi-instance MySQL login method

Multi-instance MySQL local login

[[email protected] ~]# mysql -uroot -p -S /data/3306/mysql.sock[[email protected] ~]# mysql -uroot -p -S /data/3307/mysql.sock

Tip: Multiple instances via MySQL-s specify a different sock file login

Note: Multiple instance remote connections do not need to specify a sock path

mysql -uroot -p -h 192.168.1.115 -P3306

7.3 Use MySQL help command

The help command in MySQL is similar to the man in the Linux command line, and you want to see the syntax for commands in MySQL, so you need to use HELP,HELP to follow the commands and command combinations. For example: Help create.

[Email protected] 09:4813->help

7.4 Setting and modifying the MySQL root user password

7.4.1 MySQL Database user security Policy introduction

After installing the MySQL database, the default administrator root password is empty, very insecure, so to set a password, after installing MySQL Single instance, we have done some security measures, for example:

A. Setting a password for root

B. Deleting a user account in a useless MySQL library

C. Delete the test database that exists by default.

In addition to the methods above, for MySQL database user processing, we also need to remove the root Add new Admin user.

(1) Delete all users in MySQL, including the root super user.

[email protected] 10:5249->delete from mysql.user;Query OK, 0 rows affected (0.00 sec)

(2) Add system and upgrade to Super Administrator, and root equivalent user.

grant all privileges on *.* to ‘system‘@‘localhost‘ identified by ‘123456‘ with  grant option;

7.4.2 setting a password for an administrator

mysqladmin -u root password ‘zbf666‘ 没有密码的用户设置密码。mysqladmin -u root -S /data/3307/mysql.sock password ‘123456‘ 多实例设置密码。

7.4.2.1 the admin root password outside the command line

mysqladmin -usystem -p123456 password ‘zbf666‘mysqladmin -usystem -p123456 password ‘zbf666‘ –S /data/3306/mysql.sock 适合多实例

7.4.2.2 SQL statement Modify Administrator password

[email protected] 11:4303->update mysql.user set password=password("wwn520") where user=‘system‘ and host=‘localhost‘;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0[email protected] 11:4321->flush privileges;刷新到数据文件Query OK, 0 rows affected (0.00 sec)

Tips:

1. The WHERE condition must be specified.

2. You must use the password () function to encrypt the change password.

Note: If it is a production environment, it should be at least 8 digits and have an alphanumeric mix.
This method can use-skip-grant-tables to retrieve the password.

7.4.2.3 the third method to modify the administrator password

Seldom in this way

[email protected] 11:4358->set password=password("zbf666");Query OK, 0 rows affected (0.00 sec)[email protected] 11:4845->flush privileges;Query OK, 0 rows affected (0.00 sec)

7.5 Recover the lost MySQL root user password

7.5.1 start modifying the missing MySQL Single instance root password method

2. First stop the database

   [[email protected] ~]# /etc/init.d/mysqld stopShutting down MySQL. SUCCESS!

4. Start MySQL with--skip-grant-tables

   [[email protected] ~]# mysqld_safe --skip-grant-tables --user=mysql &[[email protected] ~]#Mysql 登录时空密码

6. Login MySQL

     [[email protected] ~]# mysqlwelcome to the MySQL monitor without a password. Commands End With; or \g.your MySQL connection ID is 1Server version:5.1.72-log Source distributioncopyright (c), +, Oracle and/or I TS affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement. [email protected] 12:3556->  

8. Change the root password to the new password

     [email protected] 12:3838->set Password=password ("zbf666"); ERROR 1290 (HY000): The MySQL server is running with the--skip-grant-tables option so it cannot execute this statement[em Ail protected] 12:3913->update Mysql.user set Password=password ("zbf666") where user= ' system ' and host= ' l= ' localhost '; Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0[email protected] 12:3936->flush privil Eges; Query OK, 0 rows Affected (0.00 sec)  

10. Restart Service on Login

    [[email protected] ~]# mysqladmin -usystem -pzbf666 shutdown;180118 00:42:53 mysqld_safe mysqld from pid file /usr/local/mysql/var/localhost.localdomain.pid ended[1]+  Donemysqld_safe --skip-grant-tables --user=mysql[[email protected] ~]# /etc/init.d/mysql start[[email protected] ~]# mysql -usystem -pzbf666Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.1.72-log Source distributionCopyright (c) 2000, 2013, 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.[email protected] 12:4329->

7.5.2 multi-instance MySQL startup modify lost root password

1. Turn off MySQL

Killall mysqld

2. Add-skip-grant-tables parameter at startup, specify 3306 configuration file

Mysql_safe –defaults-file=/data/3306/my.cnf –skip-grant-table &

3. Log in to the database

Mysql –u root –p –S /data/3306/mysql.sock 登录时空密码

4. Modify the database password

mysql> update mysql.user set password=password("zbf") where user=‘root‘;Query OK, 5 rows affected (0.00 sec)Rows matched: 5  Changed: 5  Warnings: 0mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)

4. Restart Service login with new password

Killall MYSQD

Single instance:/etc/init.d/mysqld restart

Multi-instance:/data/3306/mysql restart

Mysql DBA Advanced Operations Learning Note-mysql Common Basic command combat