Tag Archives: DBA

MySQL DBA commonds

Create user and grand privileges;

mysql> create user wp_blog_usr@localhost identified by '123qwe';
Query OK, 0 rows affected (0.05 sec)

mysql> grant all privileges on wp_blog.* to wp_blog_usr@'localhost';
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;

By default in MySQL server remote access is disabled. To provide a remote access to user is:

  1. comment this line in /etc/my.cnf:
    # bind-address = 127.0.0.1

  2. grant pivileges for user:
    GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'IP' IDENTIFIED BY 'PASSWORD';
    Where IP is the IP you want to allow acess and USERNAME is the user you use to connect If you want to allow access from any IP just put % instead of your IP

  3. restart mysql server

To tell the server to reload the grant tables, perform a flush-privileges operation. This can be done by issuing a FLUSH PRIVILEGES statement or by executing a mysqladmin flush-privileges or mysqladmin reload command.

Change root password

mysqladmin -u root password 123qwe;

See full query from show processlist

show full processlist;

So how many processes or connections are now actually doing anything? We now must check for ‘Threads_running’.

mysql> SHOW GLOBAL STATUS LIKE 'Threads_running';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Threads_running | 24    |
+-----------------+-------+
1 row in set (0.00 sec)

And so we have Threads_cached, Threads_connected & Max_used_connections.

Reset Forgotten MySQL Root Password

# start up the mysql daemon and skip the grant tables which store the passwords.
mysql_safe --skip-grant-tables &

# connect to mysql without a password.
mysql --user=root mysql

# update password
UPDATE user SET password=PASSWORD('new-password') WHERE user='root';
flush privileges;
exit;

Some related links:

MySQL terminology: processes, threads & connections