Tag Archives: mysql

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

How to shrink/purge ibdata1 file in MySQL

That ibdata1 isn’t shrinking is a particularly annoying feature of MySQL. The ibdata1 file can´t actually be shrunk unless you delete all databases, remove the files and reload a dump.

But you can configure MySQL so that each table, including its indexes, is stored as a separate file. In that way ibdata1 will not grow as large. this is enabled by default as of version 5.6 of MySQL.

It was a while ago I did this. However, to setup your server to use separate files for each table you need to change my.cnf in order to enable this:

[mysqld]
innodb_file_per_table

http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html

As you want to reclaim the space from ibdata1 you actually have to delete the file:

  1. Do a mysqldump of all databases, procedures, triggers etc except the mysql and performance_schema databases
  2. Drop all databases except the above 2 databases
  3. Stop mysql
  4. Delete ibdata1 and ib_log files
  5. Start mysql
  6. Restore from dump

When you start MySQL in step 5 the ibdata1 and ib_log files will be recreated.

Now you’re fit to go. When you create a new database for analysis, the tables will be located in separate ibd* files, not in ibdata1. As you usually drop the database soon after, the ibd* files will be deleted.