MySQL : ERROR 3 (HY000): Error writing file

In one of  my development server my developers not able create a new table from Heidisql Tool.When I checked it, the server seems working fine.

Error is ,  ERROR 3 (HY000): Error writing file './backup/updateSites1.frm'

But when I checked the server disk it is almost full after I cleared the unwanted files they can able to create new table.

Issue because of Disk full so from MySQL could not create new table.

MySQL Can't create a new thread (errno 11)

In one of my MySQL Production server, I have faced an issue that MySQL could not create a new thread.Below the error i got in mysql error log file.

Error : Can't create a new thread (errno 11); if you are not
out of available memory, you can consult the manual for a possible OS-dependent bug

Solution :

1.Check your hardlimit and softlimit for MySQL user in the server.
#ulimit -a mysql

2.Change the threads in OS level,edit the below file and add the bold lines in the file.

#vi /etc/security/limits.conf


  mysql     soft     nofile     30000
  mysql     hard    nofile     30000
  mysql     soft     nproc     30000
  mysql     hard    nproc     30000


Restart MySQL

Then MySQL works fine without any error.

Its happened because of the linux server has limited connections to handle for MySQL user.After increased the value in server the error didn’t occurs.

MySQL Encrypted Database Backup for Mysqldump and Xtrabackup

What does Database Encryption and Decryption mean?

Database encryption is the process of converting data, within a database, in plain text format into a meaningless cipher text by means of a suitable algorithm.

Database decryption is converting the meaningless cipher text into the original information using keys generated by the encryption algorithms.

Database encryption can be provided at the file or column level.


Here am going to explain how to encrypt your mysql databases with mysqldump and xtrabackup.first we will see for mysqldump

MySQLdump Encryption & Decryption:

Mysqldump is an effective tool to backup MySQL database. It creates a *.sql file with DROP table, CREATE table and INSERT into sql-statements of the source database.

To encrypt your database here I have used ccrypt.ccrypt is a utility for the secure encryption and decryption of files and streams.

I have used Fedora 20 server to do this process.

First install ccrypt

#yum install ccrypt

am already have an mysql server in this machine so i will just show you how to take encrypted backup with ccrypt.

Create a hidden keyfile.

#vi /etc/key/.backupkey

in this file add any content like below,

ex : Ukl8GiJ4Q#uy@iP

Now give permission for this file,

#chmod 600 /etc/key/.backupkey

Now we can encrypt our backup,

Encryption:

#mysqldump -u root -ppa$$123 --databases country world | ccrypt -k /etc/key/.backupkey > backup_c.sql

This backup.sql will be now an encrypted backup when you open it, it will be a non readable format.

Decryption:

#cat backup_c.sql | ccrypt -d -k /etc/key/.backupkey > backup_d.sql 

Now you can see the real content in this file.
 

Compressed Mysqldump Encrypted backup:

Encryption:

#mysqldump -u root -ppa$$123 --databases country world | ccrypt -k /etc/key/.backupkey | gzip -c > backup_c.sql.gz

Decryption:

#gunzip  backup_c.sql.gz > backup_c.sql

#cat backup_c.sql | ccrypt -d -k /etc/key/.backupkey > backup_d.sql 

Xtrabackup Encryption & Decryption:
 
Percona XtraBackup is the world’s only open-source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases.

For xtrabackup you need to install latest Percona Xtrabackup(Version should be more than 2.1.4) and openssl for encryption.

Xtrabackup installation steps in this below link,
http://knowmysql.blogspot.in/2013/08/percona-xtrabackup-tool.html

Now we can encrypt our backup with openssl for Xtrabackup.

Before doing that we need to generate openssl key. 

#openssl enc -aes-256-cbc -pass pass:Prabhu123 -P -md sha1

Replace your password in the bold place,when you executed the above command  you will get the output, 

salt=76AC44528ED1441B
key=C6CA8F2AC3A824653438F7B4E2493892BCC01238E1FF4B5651588AFB6D4DA111
iv =FEC2DE284641AC8A0636D07BEEC5A514

 

with the above key we are going to encrypt with Xtrabackup

Encryption: 

#innobackupex --user=root --password=pa$$123 --export  --encrypt=AES256 --encrypt-key="FEC2DE284641AC8A0636D07BEEC5A514"  /backup
 

 after executed the above command you can see some line like below,

[01] Encrypting ./xxxx/xxxx.ibd to /backup/2014-12-27_04-49-28/xxxx/yyyy.ibd.xbcrypt
[01]        ...done
[01] Encrypting ./yyyy/aaaa.ibd to /backup/2014-12-27_04-49-28/dddd/rrrr.ibd.xbcrypt
[01]        ...done
[01] Encrypting ./rrr/ssss.ibd to /backup/2014-12-27_04-49-28/yysss/ddasffd.ibd.xbcrypt
[01]        ...done
[01] Encrypting ./gggds/ssssss.ibd to /backup/2014-12-27_04-49-28/dsfsd/fdsfds.ibd.xbcrypt
.
.
.

and at the end,

innobackupex: Backup created in directory '/backup/2014-12-27_04-49-28'
141227 04:51:51  innobackupex: Connection to database server closed
141227 04:51:51  innobackupex: completed OK!
   Now it has taken an encrypted backup.

Xtrabackup encryption with keyfile,

#openssl enc -aes-256-cbc -pass pass:Prabhu123 -P -md sha1

Replace your password in the bold place,when you executed the above command  you will get the output, 

salt=76AC44528ED1441B
key=C6CA8F2AC3A824653438F7B4E2493892BCC01238E1FF4B5651588AFB6D4DA111
iv =FEC2DE284641AC8A0636D07BEEC5A514

Now copy the iv result in a txt file,

#cat /etc/key/xtrabackupkey.txt

FEC2DE284641AC8A0636D07BEEC5A514

Now do the xtrabackup with key file, 

#innobackupex --user=root --password=pa$$123 --export  --encrypt=AES256 --encrypt-key-file=/etc/key/xtrabackupkey.txt  /backup

Decryption:

For decryption required xtrabackup 2.1.4 version.

#innobackupex  --decrypt=AES256 --encrypt-key="FEC2DE284641AC8A0636D07BEEC5A514"  /backup/2014-12-27_05-49-08/

It will extract the xtrabackup encrypted folder to decrypted folder in same location.

Compressed Xtrabackup Encrypted backup:

Encryption:

# innobackupex --user=root --password=pa$$123 --export --compress --encrypt=AES256 --encrypt-key="FEC2DE284641AC8A0636D07BEEC5A514"  /backup

Decryption:

First we need to decompress the file, see how to decompress in the below link,

http://knowmysql.blogspot.in/2013/08/percona-xtrabackup-tool.html

then decrypt by the below command with same encrypt key,

#innobackupex  --decrypt=AES256 --encrypt-key="FEC2DE284641AC8A0636D07BEEC5A514"  /backup/2014-12-27_05-49-08/

Now all the databases are decrypted in the same directory.

Create a new MySQL Server with existing MySQL data directory

Do you like to create a new server with existing MySQL data directory ?

Taking dump and import in new server will take so much time to do the process.

So better copy the data directory to new server is a good way.Below I have given the steps that how to copy the data directory and set up a new server.


1.Create a new MySQL Server.

2.Stop this newly created MySQL server.

3.Move this server data directory to some other location.now nothing should not be there in new server data directory.

4.Now copy the data directory files from the existing server it should includes ibdata,log file 0 and 1.(This server should be stop while copying the files)

5.After copied all the files, Check the owner and group name for the data directory files.If the owner and group name are in root then change it to mysql.

7.Start this new MySQL server.

8.After started check the new MySQL error logs to confirm that there is no issues.

9.Login to new MySQL server.

10.Now use the same databases and tables like existing server.

There is a tool to copy the files by taking the data directory backup with "xtrabackup" and restore it in new server. 

 

Note :

If you get this below error:

" ERROR 1146 (42S02): Table 'database.tablename' doesn't exist"

 It means that you have not copied all the files properly from the existing server.

 Now copy the all files properly and start the server and now it will works fine.

   

MySQL Grant Password Error : ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number

When you creating a new user for a MySQL server with the below method sometime you may get this below error.

Ex:

mysql>grant all privileges on *.* to 'root'@'localhost' identified by password 'welcome';

"ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number"


Solution:

mysql> select password ('welcome');

+-------------------------------------------+
| password ('welcome')           |
+-------------------------------------------+
| *DF216F57F1F2066124E1AA5491D995C3CB57E4C2 |
+-------------------------------------------+
1 row in set (0.02 sec)

mysql>grant all privileges on *.* to 'root'@'localhost' identified by password '*DF216F57F1F2066124E1AA5491D995C3CB57E4C2';

 Query OK, 0 rows affected (0.02 sec)

mysql>flush privileges;

 Query OK, 0 rows affected (0.02 sec)


Now the error gone.Try this when you are getting this error.


Better always try this below way to create MySQL user.

mysql>grant all privileges on *.* to 'root'@'localhost' identified by 'welcome';

Query OK, 0 rows affected (0.00 sec)

mysql>flush privileges;

Query OK, 0 rows affected (0.02 sec)

Percona Xtrabackup Tool

We all know to take backup MySQL databases through Mysqldump options but here I am going to explain another tool called Xtrabackup to backup our MySQL databases without any disturbance to your Server.

We can download Xtrabackup from this link : http://www.percona.com/downloads/XtraBackup/


Xtrabackup RPM  Installation:

In RPM there is 3 files:

1.Xtrabackup ( percona-xtrabackup-2.1.2-611.rhel6.x86_64.rpm )
2.Debug-files (percona-xtrabackup-debuginfo-2.1.2-611.rhel6.x86_64.rpm )
3.Test (percona-xtrabackup-test-2.1.2-611.rhel6.x86_64.rpm )

Download these above three files.

Installation :

Step 1:

$ rpm -Uvh percona-xtrabackup-2.1.2-611.rhel6.x86_64.rpm

Step 2:

$ rpm -Uvh  percona-xtrabackup-debuginfo-2.1.2-611.rhel6.x86_64.rpm

Step 3:

$ rpm -Uvh  percona-xtrabackup-test-2.1.2-611.rhel6.x86_64.rpm


After installed it check it whether it is installed or not by using,

$ yum list|grep percona

Here you can see the list for percona files was installed.


Take Backup Using Xtrabackup:

Add your data directory in my.cnf or my.ini file.

$ innobackupex --user=root --password=pa$$word --export  /nfsdrive

/nfsdrive  --> backup path.

Take compressed backup:

$ innobackupex --user=root --password=pa$$word --export --compress /nfsdrive

or

$ innobackupex --user=root --password=pa$$word --stream=tar ./ | gzip -c > /nfsdrive/backup.tar.gz


Restore Backup:

1.Stop mysql
2.Move your mysql data directory to other location.

3.Copy the backup from backup path to mysql data directory.
4.Start the server.

Now you can use your databases.

Note:

If you are using "--compress" option while taking backup.It will save in ".qp" format you need to extract it by download qpress and extract it by,

/path/qpress  -d  /backup_path/example.qp  /backup_path

More details : http://www.percona.com/software/percona-xtrabackup

KNOW ABOUT INFORMATION_SCHEMA IN MySQL

INFORMATION_SCHEMA is the information database, the place that stores information about all the other databases that the MySQL server maintains. Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not base tables, so there are no files associated with them, and you cannot set triggers on them. Also, there is no database directory with that name.

You can do only select INFORMATION_SCHEMA as the default database with a USE statement, you can only read the contents of tables, not perform INSERT, UPDATE, or DELETE operations on them.

1.How to view Table engines using Information_schema for a table.

mysql>SELECT table_name, table_type, engine FROM information_schema.tables WHERE table_schema 'mysql' ORDER BY table_name DESC;

2.Find the Column using Information_schema for a table .

mysql>select table_schema "Data Base Name",table_name,column_name from information_schema.columns where column_name LIKE 'dl;

3.Find the Table in which database it has.

mysql>select table_schema "Data Base Name",table_name from information_schema.tables where table_name='plugin';

We can use a lot of things using information_schema.In the above I gave three examples about information_schema.I think it will help you more.

MySQL Clients and non-clients programs.

MySQL Clients:

      In MySQL Client programs are programs that can be used for communicating with the server to manipulate the information in the databases that the server manages.

• MySQL Query Browser and MySQL Administrator are graphical front ends to the server. 
• mysql is a command-line program that acts as a text-based front end for the server. It's used for issuing queries and viewing the results interactively from a terminal window. 
• mysqlimport for importing data files.
•  mysqldump for making backups.
•  mysqladmin for server administration.
•  mysqlcheck for checking the integrity of the database files.

 MySQL non-clients:

         In MySQL non-client utilities are programs that act independently of the server. They do not operate by first establishing a connection to the server. myisamchk is an example. It performs table checking and repair operations. Another program in this category is myisampack, which creates compressed read-only versions of MyISAM tables. Both utilities operate by accessing MyISAM table files directly, independent of the mysqld (database server).