Category 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.

mysqldump –where option

The MySQL provides a great tool mysqdump to dump database. Its the official sql dump utitlity for MySQL database. It makes the life of dba so easy that he can backup and restore the database within just two commands. But sometimes due to the lacking of the infrastructure you can not dump and restore that easily. Specially when you are dealing with huge amount of data. Our database grows over time. Few hundred GBs are quite common. But if you run a software for long it might get in to Tera byte range. The problem starts when size is this huge.

Split the big tables by tables.

mysqldump db1 table1 table2 table3 > table1-3.sql

Split the big tables by rows with –where.

-w, --where=name    Dump only selected records. Quotes are mandatory.

If your table has auto column typed with auto_increment you can split by any number of chunks.

mysqldump --where "id%2=0" db1 table1 > table1_even.sql
mysqldump --where "id%2=1" db1 table1 > table4_odd.sql

limit clause.

# Dump the first 100000 rows from the table named table5 into the file dump.sql
# Use LIMIT [OFFSET, ] LIMIT So some thing like that:
mysqldump --where "1 LIMIT 10000" database1 table5 > dump.sql

Others. As the –where switch allows any sql condition you can use any criteria.

mysqldump --where "year(date) <= 2008" db1 payment > payment_prior_2009.sql

 Some useful options:

–skip-add-drop-table Avoid generate the DROP TABLE statements.
–skip-create-options Disable include all MySQL specific create options.
–replace Use REPLACE INTO instead of INSERT INTO.

MySQL exporting and importing data

In this part of the MySQL tutorial, we will be exporting data from MySQL
database and importing data back.

Simple data export

In our first example, we will save data in a text file.

mysql> SELECT * FROM Cars INTO OUTFILE '/tmp/cars';
Query OK, 8 rows affected (0.00 sec)

We select all rows (8) from the Cars table into the cars file located
in the /tmp directory. We need to have permissions to write to that directory.

$ cat /tmp/cars
1       Audi    52642
2       Mercedes        57127
3       Skoda   9000
4       Volvo   29000
5       Bentley 350000
6       Citroen 21000
7       Hummer  41400
8       Volkswagen      21600

We show the contents of the file.

mysql> DELETE FROM Cars;

mysql> LOAD DATA INFILE '/tmp/cars' INTO TABLE Cars;

In the first statement we delete all rows from the table.
In the second statement we load all data from the text file into
the Cars table.


mysql> SELECT * FROM Cars INTO OUTFILE '/tmp/cars.csv'
    -> FIELDS TERMINATED BY ',';

In the above SQL statement, we dump all data from the Cars table
into a cars.csv file. The FIELDS TERMINATED BY clause
controls, how the data will be terminated in the text file. We have
chosen a comma character. The csv stands for Comma Separated Values and
it is a very common and very portable file format. It can be imported
by numerous other applications. Like OpenOffice, other databases etc.

$ cat /tmp/cars.csv 
1,Audi,52642
2,Mercedes,57127
3,Skoda,9000
4,Volvo,29000
5,Bentley,350000
6,Citroen,21000
7,Hummer,41400
8,Volkswagen,21600

This is the contents of the cars.csv file.

mysql> DELETE FROM Cars;

mysql> LOAD DATA INFILE '/tmp/cars.csv' INTO TABLE Cars
    -> FIELDS TERMINATED BY ',';

mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id | Name       | Cost   |
+----+------------+--------+
|  1 | Audi       |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda      |   9000 |
|  4 | Volvo      |  29000 |
|  5 | Bentley    | 350000 |
|  6 | Citroen    |  21000 |
|  7 | Hummer     |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+

We delete all the data and restore it from the cars.csv file.

Exporting to XML files

It is possible to export and import XML data using the mysql monitor.

$ mysql -uroot -p --xml -e 'SELECT * FROM mydb.Cars' > /tmp/cars.xml

The mysql monitor has an –xml option, which enables us to dump data in XML format.
The -e option executes a statement and quits the monitor.

$ cat /tmp/cars.xml 
<?xml version="1.0"?>

<resultset statement="SELECT * FROM mydb.Cars
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
        <field name="Id">1</field>
        <field name="Name">Audi</field>
        <field name="Cost">52642</field>
  </row>

  <row>
        <field name="Id">2</field>
        <field name="Name">Mercedes</field>
        <field name="Cost">57127</field>
  </row>

  ......

</resultset>

This is the XML file generated by the mysql monitor.

mysql> TRUNCATE Cars;

mysql> LOAD XML /tmp/cars.xml INTO TABLE Cars;

We truncate the Cars table. We load data from the XML file.
Note, that LOAD XML statement is available for
MySQL 5.5 and newer.

Using mysqldump tool

The mysqldump is a command tool to create backups for MySQL.
The word dump is used when we transfer data from one place to another.
From a database file to a text file. From a memory to a file. And
similar.

Dumping table structures

mysqldump -u root -p --no-data mydb > bkp1.sql

The above command dumps table structures of all tables in
the mydb database to the bkq1.sql file. The –no-data option
causes that the data is not saved, only the table structures.

--
-- Table structure for table `Cars`
--

DROP TABLE IF EXISTS `Cars`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Cars` (
  `Id` int(11) NOT NULL,
  `Name` varchar(50) DEFAULT NULL,
  `Cost` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

Here we see a portion of the bkp1.sql file. This is the SQL for the
creation of the Cars table.

Dumping data only

$ mysqldump -uroot -p --no-create-info mydb > bkp2.sql

This command dumps all data from all tables of the mydb databases.
It omits the table structures. The omission of the table structures
is caused by the –no-create-info option.

--
-- Dumping data for table `Cars`
--

LOCK TABLES `Cars` WRITE;
/*!40000 ALTER TABLE `Cars` DISABLE KEYS */;
INSERT INTO `Cars` VALUES (1,'Audi',52642),(2,'Mercedes',57127),(3,'Skoda',9000),
(4,'Volvo',29000),(5,'Bentley',350000),(6,'Citroen',21000),
(7,'Hummer',41400),(8,'Volkswagen',21600);
/*!40000 ALTER TABLE `Cars` ENABLE KEYS */;
UNLOCK TABLES;

Here we can see the data for the Cars table.

Dumping the whole database

$ mysqldump -uroot -p mydb > bkp3.sql

This command dumps all tables from the mydb database to the
bkp3.sql file.

Restoring data

We show, how to restore the database from the backup SQL files.

mysql> DROP DATABASE mydb;
ERROR 1010 (HY000): Error dropping database (can't rmdir './mydb/', errno: 17)

mysql> SHOW TABLES;
Empty set (0.00 sec)

We drop the mydb database. An error is shown. The tables were dropped but
not the database.

$ sudo ls /var/lib/mysql/mydb
cars  cars.txt
$ sudo rm /var/lib/mysql/mydb/cars
$ sudo rm /var/lib/mysql/mydb/cars.txt

The reason is that (in my case) while doing backups, some of the data were
written in the mydb directory, in which MySQL stores the mydb database.
These two alien files could not be removed, hence the above error. By
removing the files the error is fixed.

mysql> DROP DATABASE mydb;
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| testdb             |
| world              |
+--------------------+
4 rows in set (0.00 sec)

The mydb database was fully removed.

mysql> CREATE DATABASE mydb;

mysql> USE mydb;

mysql> source bkp3.sql

We create the mydb database. Change to the database. And
use the source command to execute the bkp3.sql script.
The database is recreated.

mysql> SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| AA             |
| Ages           |
| Animals        |
| Authors        |
| BB             |
| Books          |
| Books2         |
| Brands         |
| Cars           |
...

mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id | Name       | Cost   |
+----+------------+--------+
|  1 | Audi       |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda      |   9000 |
|  4 | Volvo      |  29000 |
|  5 | Bentley    | 350000 |
|  6 | Citroen    |  21000 |
|  7 | Hummer     |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+

The data is verified.

phpMyAdmin – #2002 Cannot log in to the MySQL server

终端下可以登录mysql,但是用phpmyadmin无法登录提示:#2002 Cannot log in to the MySQL server

修改config.inc.php文件,

将$cfg['Servers'][$i]['host'] = ‘localhost’;
改为$cfg['Servers'][$i]['host'] = ’127.0.0.1′;
————————————————–

修改 php.ini: mysql.default_socket = /tmp/mysql.sock

ps: 主要原因是因为本地 xampp 的 mysql 服务配置 my.cnf
socket = /Applications/XAMPP/xamppfiles/var/mysql/mysql.sock

把路径 ln -sf 到 /tmp/mysql.sock 也可以。