ibdata1 mysql file

Posted by | Posted in Code, Command-Line, Linux, MySQL, Ubuntu | Posted on 31-07-2014

Recently I discovered that my ibdata1 file with in /var/lib/mysql was filling up my webserver hard drive. After reading on several forums, it was discovered that the ibdata1 file contains all of the InnoDB file structure as-well-as all of the data. You can not delete this file and have mysql recreate it. You will lose all of your MySQL InnoDB information.

Instead, you’ll have to backup all of your InnoDB databases, drop all of your InnoDB databases, stop the mysql server, remove the corresponding files, restart myssql, recreate your databases and then restore all your databases.

Below, I’ll demonstrate the steps that I took to accomplish this task.

Backing up your InnoDB MySQL Database

mysqldump -u USER --password=PASS DATABASE > DATABASE_DATE.sql

Drop your MySQL InnoDB Database

mysql -u USER --password=PASS
drop database DATABASE;
exit;

Stop MySQL Server

sudo service mysql stop

Remove the offending files

sudo rm /var/lib/mysql/ibdate1
sudo rm /var/lib/mysql/ib_logfile0
sudo rm /var/lib/mysql/ib_logfile1

Restart MySQL

sudo service mysql start

Recreate your InnoDB MySQL Database

mysql -u USER --password=PASS
create database DATABASE;
exit;

Restore your database from backup.

cat DATABASE_DATE.sql | mysql -u USER --password=PASS -D DATABASE

Now you should be all set and your ibdata1 file will be MUCH smaller.

Backing up and Restoring your MySQL database – Ubuntu

Posted by | Posted in Bash, Code, Command-Line, Guides, Linux, MySQL, Ubuntu | Posted on 23-07-2009

If you manage any type of databases, you know the importance of backing up your data. Whether you have file corruption from a failing hard drive, a run away script or from a hacking attempt; loosing your databases can and will break your business. If you’ve never backed up your database, now is a good time to start.

The easiest way to backup your MySQL database is with “/usr/bin/mysqldump”. You’ll need to access this application via command. Whether it is from the a console or an SSH connection.

mysqldump – a database backup program
mysqldump [options] [db_name [tbl_name ...]]

There are three general ways to invoke mysqldump:

mysqldump [options] db_name [tables]
mysqldump [options] --databases db_name1 [db_name2 db_name3...]
mysqldump [options] --all-databases

By default, the mysqldump command will dump the contents of your database directly into your terminal and not into a file. This output can be redirected anyway and to anywhere you like. But it is commonly used in this manner:

mysqldump -u [username] -p [password] [databasename] > [backup.sql]
mysqldump -u root -p PASSWORD DATABASE > DATABASE-BACKUP-DATE.sql

[username] – your database username
[password] – the password for your database
[databasename] – the name of your database
[backup.sql] – the file to which the database contents needs to be directed to

If you don’t want to use your password in the command line, you can always prompt for your password by excluding it from your command.

mysqldump -u root -p DATABASE > DATABASE-BACKUP-DATE.sql
Enter password:

If you forgot your password, you’ll get an error like this.

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect

Or if you didn’t enter a password when one is needed, you’ll get this error.

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect

If you’d like the convenience of having the database automatically delete the old database before re-writing the content you can use the “–add-drop-table” feature.

 mysqldump --add-drop-table -u root -p PASSWORD DATABASE > DATABASE-BACKUP-DATE.sql

If you need to only backup specific tables of the database you can use this command.

mysqldump --add-drop-table -u root -p PASSWORD DATABASE TABLE> DATABASE-TABLE-BACKUP-DATE.sql

The syntax for the command is:

mysqldump -u [username] -p [password] [databasename] [table1 table2 ....]

[tables] – This is a list of tables to backup. Each table is separated by a space.

If you are a server administrator and need to backup multiple databases at the same time, you can use the next command. You just have to be sure that the “-u” user account has permission to read those databases.

mysqldump -u root -p PASSWORD --databases DATABASE1 DATABASE2 DATABASE3 > DATABASE-1-2-3-BACKUP-DATE.sql

Or if you just want to back up ALL the databases at one, use the “–all-databases” parameter to backup all the databases in the server in one easy step.

mysqldump --all-databases> ALL-DATABASES-DATE.sql

How to back up only the data structure.

While in development, most programmers only need to back up their database structure, not their test data. To do this, use the “–no-data” parameter.

mysqldump --no-data --databases DATABASE1 DATABASE2 DATABASE3 > DATABASE-1-2-3-Structure-Bakcup-DATE.sql

If you have large databases and not enough hard drive space to store them in the regular SQL, text format, you can compressing the backup file on the fly.

mysqldump --all-databases | bzip2 -c >all-databases-backup-date.sql.bz2
 
mysqldump --all-databases | gzip >all-databases-backup-date.sql.gz

If you are away from your servers, or have multiple servers that you manage, you can still backup your databases while you are away. Use the bash script below in conjunction with a cron-tab to automate your backups.

#!/bin/sh
date=`date -I`
mysqldump --all-databases | gzip > /var/mysql_backup/mysql-backup-all-$date.sql.gz

How to restore your databases.

If the unforeseen happens and you need to totally rebuild your databases, you can do so easily from the mysqldump SQL files.

mysql -u root -p PASSWORD DATABASE < DATABASE-BACKUP-DATE.sql

Mysql Synopsis:

mysql -u [username] -p [password] [database_to_restore] < [backup_file.sql]

Restoring from the compressed files.

gunzip < DATABASE-BACKUP.sql.gz | mysql -u root -p PASSWORD DATABASE

Or do 2 at a time.

cat DATABASE-BACKUP-1.sql DATABASE-BACKUP-2.sql | mysql -u root -p PASSWORD

A beginners guide to setting up MySQL – Ubuntu

Posted by | Posted in Code, Command-Line, Guides, Linux, MySQL, Ubuntu | Posted on 20-07-2009

MySQL is a very popular and widely used database system that can be used on almost any operating system, but primarly MySQL is used on LAMP (Linux/Apache/MySQL/PHP) systems.

Setting up a MySQL server is fairly easy. If you already know how to work a commmand-line interface, then you are well on your way to setting up a MySQL system. There are (4) things that you need to do, in-order to use a MySQL database system.

1) Install MySQL Server
2) Set a password for the root account
3) Set limitation privileges on the root account
4) Create a database

Installing mysql server via apt-get

$ sudo apt-get install mysql-server

On a default install, the root users does not need a password to access any databases and you can login with no password like this.

$ mysql -u root

If a password is required, use the extra switch -p:

$ mysql -u root -p
Enter password:

To add a root password when there isn’t one. Use the following command. Be sure to change the PASSWORD to your own password and to make sure that it is encapsulated with quotation marks.

mysqladmin -u root password "PASSWORD"

Now that we have the root account locked down with a password, we need to change its accessibility. While you are logged into mysql used these following commands to secure the root account.

mysql> use mysql;
mysql> delete from user where Host like "%";
mysql> grant all privileges on *.* to root@"%.DOMAIN.COM" identified by 'PASSWORD' with grant option;
mysql> grant all privileges on *.* to root@localhost identified by 'PASSWORD' with grant option;
mysql> flush privileges;
mysql> exit;

Now that we can login and feel secure, we create a database. Change DATABASE to any name you’d like.

mysql> create database DATABASE;
Query OK, 1 row affected (0.00 sec)

And that’s it. You can now check that you can connect to the MySQL server using this command:

$ mysql -u USER -p 'PASSWORD' DATABASE_NAME
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 61
Server version: 5.0.75-0ubuntu10.2 (Ubuntu)
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql>

Or, If you want to check your version

mysqladmin -u root -p version
 
Enter password: 
mysqladmin  Ver 8.41 Distrib 5.0.75, for debian-linux-gnu on i486
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
 
Server version          5.0.75-0ubuntu10.2
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 3 hours 7 min 4 sec
 
Threads: 2  Questions: 6596  Slow queries: 0  Opens: 969  Flush tables: 1  Open tables: 19  Queries per second avg: 0.588

If you need to start|stop|restart MySQL, use the commands in /etc/init.d/mysql

sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start
sudo /etc/init.d/mysql restart

Notice :

preforming the above security options for the root account will break the /etc/init.d/mysql scripts.

sudo ./mysql status
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'
 * 
@:/etc/init.d$ sudo /etc/init.dmysql restart
 * Stopping MySQL database server mysqld
   ...fail!
 * Starting MySQL database server mysqld
   ...done.
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'

To fix this issue, you need to edit the /etc/mysql/debian.cnf file. There are (2) lines that need to be changed. Change the password hash to a new password. Unfortunately, this password is plain text.

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = XXXXXXXXXXXXX
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
user     = debian-sys-maint
password = XXXXXXXXXXXXXX
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

Then you need to log into MySQL and permissions to the “debian-sys-maint” user account.

> GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'NEWPASSWORD' WITH GRANT OPTION
 
> FLUSH PRIVILEGES;

Now, if for some reason, you overwrite, or toast your root password, You need to reset it. At this point in time, the start|stop features of /etc/init.d/mysql will probably no longer work and you need to kill the process old school style.

ps -aux |grep mysql
 
root      5340  0.0  0.0   1872   548 pts/2    S    05:17   0:00 /bin/sh /usr/bin/mysqld_safe
mysql     5379  0.0  3.5 129412 27116 pts/2    Sl   05:17   0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock
root      5381  0.0  0.0   3052   704 pts/2    S    05:17   0:00 logger -p daemon.err -t mysqld_safe -i -t mysqld
root      5543  0.0  0.1   3336   792 pts/2    R+   05:23   0:00 grep mysql

What you are seeing here is a list of processes that contain the word “mysql”. From left to right you can see there is a USER and the PROCESS ID (4 digits), LOAD variables, RUN time and then the COMMAND. What we are interested in is the (4) digit process ID numbers. In this case, 5340, 5379 and 5381. The 5543 process, as you can see is “grep mysql”. This is our search process ID and most likely doesn’t exist anymore.

In-order to stop those (3) processes, you need to kill them. Literally. You need to use the “kill” command to stop the process from running, and to keep it from relaunching, you add the “-9″. This refers to the type of kill you are preforming. In this case the kill cannot be blocked and won’t relaunch.

# sudo kill -9 5340
# sudo kill -9 5379
# sudo kill -9 5381
kill: No such process

As you can see the 5381 process no longer existed after we killed the 5379 process.

Now that we have mysql completely stopped, we can restart the mysql server and change the root password. To be able to do this start mysql with the “–skip-grant-tables” followed by the “&” character. The skip grant tables will bypass the mysql database and won’t load the current usernames and passwords. the “&” will embed the process in the background, so you can keep working from the same command-shell.

sudo mysqld --skip-grant-tables &
 
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
090720  5:42:50  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
090720  5:42:50  InnoDB: Started; log sequence number 0 115369069
090720  5:42:50 [Note] mysqld: ready for connections.
Version: '5.0.75-0ubuntu10.2'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

Now that MySQL is restarted, go ahead and log back into the server and use the mysql database. Notice, we don’t need a password this time.

mysql -u root
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.75-0ubuntu10.2 (Ubuntu)
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> use mysql;
 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed

Now lets change the root password.

 
mysql> UPDATE user SET password = password('NEW PASSWORD') WHERE user = 'root' AND host='localhost';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

The Rows mached:1, means (1) row was updated and the password was updated successfully!

Flush your privileges and quit.

mysql> FLUSH privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> quit;
Bye

Now restart your mysql server to reactivate the accounts and passwords.

sudo /etc/init.d/mysql restart
 * Stopping MySQL database server mysqld
090720  5:52:22 [Note] mysqld: Normal shutdown
090720  5:52:22  InnoDB: Starting shutdown...
090720  5:52:23  InnoDB: Shutdown completed; log sequence number 0 115369069
090720  5:52:23 [Note] mysqld: Shutdown complete
   ...done.
 * Starting MySQL database server mysqld
   ...done.
 * Checking for corrupt, not cleanly closed and upgrade needing tables.
[1]+  Done                    sudo mysqld --skip-grant-tables

Using MyTop – Ubuntu

Posted by | Posted in Command-Line, Linux, MySQL, Ubuntu | Posted on 20-07-2009

If you ever need to know how well your databases are being queried, mytop is the key. MyTop is a clone of Top for MySQL.

Your Ubuntu server won’t come pre-installed with mytop, so you’ll have to to apt-get to install it.

/usr/bin/sudo /usr/bin/apt-get install mytop

Once mytop is installed and your mysql server is setup, you can view your database stats.

-u : user account
-p : password
-h : server, normally localhost
-d : database you want to view
-P : port, if different from default

mytop -u USER -h SERVER -d DATABASE -p PASSWORD

You should see something like this come up.

MySQL on localhost (5.0.75-0ubuntu10.2)    up 0+00:32:06 [02:09:34] Queries: 5.0
qps:    0 Slow:     0.0         Se/In/Up/De(%):    00/00/00/00   qps now:    0 
Slow qps: 0.0  Threads:    1 (   1/   0) 00/00/00/00  Key Efficiency: 90.0%  
Bps in/out:   0.1/  6.9   Now in/out:   8.3/ 1.3k
 
 Id      User         Host/IP         DB      Time    Cmd Query or State 
 --      ----         -------         --      ----    --- ----------  
38      root       localhost      mysql         0  Query show full processlist

to exit mytop, press the “Q” key.

Using PHP to Connect to a MySQL server

Posted by | Posted in Code, MySQL, PHP | Posted on 18-07-2009

This is how I normally link PHP to my MySQL servers. I use this code normally for my shell scripting, but it also works for websites.

$mysql_server = "SERVER_ADDRESS";
$mysql_user = "SERVER_USER";
$mysql_password = "SERVER_PASSWORD";
$mysql_database = 'SERVER_DATABASE';
$mysql_table = 'SERVER_TABLE';
$mysql_connection = @mysql_connect($mysql_server,$mysql_user,$mysql_password);
 
if (!$mysql_connection)
  DIE ('ERROR 10: No Connection to '.$mysql_server.' '. mysql_error() ."\n");
 
$mysql_database_connection = @mysql_select_db($mysql_database,$mysql_connection);
 
if (!$mysql_database_connection)
  DIE ('ERROR 30 : Could not select database '. $mysql_database .' '. mysql_error() ."\n");
 
echo "MySQL : Connection Active \n";

Join the mailing list

Check your email and confirm the subscription