If you no longer need the data inside the database’s tables (unlikely), simply add –no-data switch to export only the tables’ structures.
mysqldump –no-data -u root -p –databases db1 db2 db3 db3 db4 > /tmp/mysql_backup.txt
Archive for the ‘Mysql’ category
How to Export A MySQL Multiples Databases Structures Only
March 10th, 2011Install MySQL 5.5 on Ubuntu
March 8th, 2011I followed the steps on this page to install MySQL 5.5 on Ubuntu.
To install and use a MySQL binary distribution, the basic command sequence looks like this:
shell> groupadd mysql
shell> useradd -r -g mysql mysql
shell> cd /usr/local
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> scripts/mysql_install_db –user=mysql
shell> chown -R root .
shell> chown -R mysql data
# Next command is optional
shell> cp support-files/my-medium.cnf /etc/my.cnf
shell> bin/mysqld_safe –user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server
Here are some problems I had during the installation.
1. mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
I Fixed after installing libaio1: sudo apt-get install libaio1
2. When I was trying to run /usr/local/mysql/scripts/mysql_install_db –user=mysql to reate the data dictionary
This command gives the following error:
FATAL ERROR: Could not find mysqld
The following directories were searched:
/usr/libexec
/usr/sbin
/usr/bin
Fix: Find and remove the old my.cnf file.
sudo rm /etc/mysql/my.cnf, because it was getting the details from the /etc/my.cnf
Don’t forget to set your root password.
./bin/mysqladmin -u root password ‘new-password’
mysqld_safe A mysqld process already exists
March 5th, 2011Today I faced issue again with MySQL running of NFS .I was trying to shutwon mysql
using /etc/rc.d/init.d/mysql stop
MySQL shutdown was successful .
after making changes in my.cnf file I tried to start MySQL using
/usr/bin/mysqld_safe –defaults-file=/etc/my.cnf &
I got error
mysqld_safe A mysqld process already exists
I checked that no other instance is running using
ps aux| grep MySQL
I got no MySQL process running, as I have faced so many errors because of the NFS
locking the files mentioned in my previous posts
http://onaxer.com/blog/blog/2011/02/26/error-1033-hy000-incorrect-information-in-file/
http://onaxer.com/blog/blog/2011/02/23/innodb-unable-to-lock-ib_logfile0-error-11/
http://onaxer.com/blog/blog/2010/06/01/innodb-unable-to-lock-ibdata1-error-37/
I tried the same solution again
1) /etc/init.d/portmap restart
2) /etc/init.d/nfslock restart
And it worked. I was able to start MySQL after that successfully.
MySQL Replication variable Seconds_Behind_Master explained
February 27th, 2011Seconds_Behind_Master variable in the MySQL Replication is often misused and misunderstood variables . Many DBA’s and Sysadmin use this variable to check the MySQL Replication latency between master and slave in different replication topologies Master -> Slave , Master-> Master etc..
So what is this variable and how it works and what it shows as per the MySQL Documentation?
this field measures the time difference in seconds between the slave SQL thread and the slave I/O thread and the value greatly depends on the network between master and slave , this value can be realistic only on the fast network. this means that Seconds_Behind_Master can not always give the correct latency between the master and slave.this is the reason if you are monitoring this variables to know the how much the slave behind master you must be getting some weird values and immediately coming to 0 .
In my article I have recommend some methods on how MySQL replication should be monitored.
ERROR 1033 (HY000): Incorrect information in file
February 26th, 2011I got this error while restoring data from Netapp snapshot backup , I restored the last days backup
on a new directory on the NFS , after the restoring the MySQL data files from Netapp I tried to start
the mysql , MySQL Started , I noticed that queries are failing , on checking the error log
I got the error ERROR 1033 (HY000): Incorrect information in file for an Innodb table
I realised that I had not created the tmp file as per given in the my.cnf , I created the /tmp
file made mysql owner of the /tmp file , restarted the MySQL and error was gone.
the other possible reasons are
1) actual corruption of the table
2) Innodb plugin regsitration failed. reasons may be various
How to quickly Restore a single table from MySQLDump Backup
February 24th, 2011Recently I had to recover a table with less then 200 records from backup file take from mysqldump of 50 GB. Normally there are different approaches which we can use to get the data from mysqldump for single table or set of tables.
1) Restore the mysqldump file on another server or same server with different name , then take the mysqldump of the tables using –tables option of mysqldump , then restore this dump file on the database we want. In mycase time was very important and I could not afford waiting for 50 Gb dump file to restore and then take backup and then restore , so this option was out of question.
2) using awk the logic here is that mysqldump has the table structure in the dump and then the actual insert commands , so we need to know the sequence of the tables in the mysqldump which will be same as of the tables listing in any tool in case you do show tables so say the table sequence in the show tables is
table1
table2
table3
and we need to restore the table2 , structure of table can be figure out using
show create table tablename
awk can be used like this
$ awk ‘/ structure for table .table2./,/structure for table .table3./{print}’ mydumpfile.sql > /tmp/recovered_table.sql
this will give all the insert commands for that table that can be restore in mysql.
This approach also could not work for me as it will take some time to parse the 50GB mysqldump.
3) Using mysql privileges .
what I did was used the mysql privileges , I created a table with the same name and structure on another Database say RecoverDB
I created a new user say recovery and in the RecoverDB database I gave all permission on the specific table only , I used the –force option of mysql which keeps the restore process continue even if there is an error in the restoration process .now from the mysqldump file I did the restoration on the RecoverDB
mysql -urecovery -ppassword RecoverDB </path of mysqldump file
as I have given write permission to only the table which I want to restore , restoration fails for all other tables except the one where
I have given priviledge I was able to restore the table in 2 mins, it was quick isn’t it . then I took the mysqldump of this table and restored on the actual DB where I wanted to restore. There might be other approaches as well and depending upon the platform (Windows, linux,solaris) the (perl,awk) might not be available (windows) but 3rd approach works irrespective of the platform.
InnoDB: Unable to lock ./ib_logfile0, error: 11
February 23rd, 2011I got the error after an hardware failure on MySQL server while I tried to restart MySQL server innodb was not showing as a plugin , on checking the error log I got “InnoDB: Unable to lock ./ib_logfile0, error: 11 “ as I use NFS to store the mysql directory , from my previous expierence which is explained in http://onaxer.com/blog/blog/2010/06/01/innodb-unable-to-lock-ibdata1-error-37 ,
I tried doing these things
first - /etc/init.d/portmap restart
Second – /etc/init.d/nfslock restart
restarted the mysql and it worked like a charm.
How to enable slow query log in MySQL 5.0
January 12th, 2011Entries required in my.cnf
log-slow-queries
If no file_name value is given for log-slow-queries, the default name is host_name-slow.log. The server creates the file in the data directory unless an absolute path name is given to specify a different directory. if we want to give the specific file name then give the path of the filename
log-slow-queries=/var/log/slow.log
in case we want to log the queries which do not use indexes
log-queries-not-using-indexes
to set the long query time in seconds , 1 sec is ideal
long_query_time =1
in mysql 5.0 long_query_time can be changed dynamically
set global long_query_time=1
Restart MySQL and slow query log will be enabled.
How to enable performance_schema in MySQL 5.5
December 29th, 2010One of the cool feature of MySQL 5.5 performance schema is not enabled by default to enable it in the my.cnf for linux add variable under mysqld
[mysqld] performance_schema to check if the performance_schema mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) enjoy mysqling ...
How to examine and kill MySQL client processes
November 26th, 2010A useful MySQL command, when it comes to keeping track of client-server transactions, is SHOW PROCESSLIST. It lists all the open connections to the server, together with a thread ID, information on the requesting host, user and database, the query string, and the current state of the connection. The command itself is simple to run from the MySQL client:
mysql> SHOW PROCESSLIST;
Note that since this command displays currently executing queries, it is only available to users with the SUPER privilege (such as the MySQL root user). A number of different states are possible for each connection —the MySQL manual lists them, together with explanations of each.
In addition to viewing processes, users with the SUPER privilege can also kill running threads—for example, threads which have “gone zombie” and are either not responding or tying up server resources—with the KILL command. To kill a thread completely, use the KILL command followed by the thread ID returned by SHOW PROCESSLIST:
mysql> KILL 27;
Query OK, 0 rows affected (0.05 sec)
To kill the query being executed by a thread but leave the connection active (yes, MySQL even allows such fine-grained control), use the KILL QUERY command instead, followed by the appropriate thread ID.
The best approach is to add mysql_close() at the end of every page.
You have also two options:
1) In ssh, mysql | kill <procid>, or
2) In my.cnf, wait_timeout = 600 (time is in seconds)
Enable read only mode on a mysql-server
November 24th, 2010Here are some useful options that can prevent writing to MySQL databases, while retaining the right reading.
The activation of these features may be particularly interesting in the case of a change of server. You can then export and import all data from one server to another, being sure to maintain data integrity. As for customers, they can only read data.
1. Method by changing a variable server
To do this, connect to the server with root user:
mysql-h localhost-u root-p
and run the command:
mysql> set GLOBAL read_only = true;
The data in all databases are accessible only for reading. Note that root guard on the other hand always have the right to write.
Example of inserting data with a simple user:
mysql> INSERT INTO foo VALUES ( ‘tata2′);
ERROR 1290 (HY000): The MySQL server is running with the-read-only
option so it can not execute this statement
To disable this mode, you can then run the following command:
mysql> set GLOBAL read_only = false;
2. Method of positioning locks
There is also a second method is to put locks via:
mysql> FLUSH TABLES WITH READ LOCK;
The latter will then close all open tables and locks all the reading tables and bases.
At this time, applications that wish to make an entry will be queued until the unlock command:
mysql> UNLOCK TABLES;
It will be possible to see a list of requests waiting in the listing process as follows:
mysql> SHOW processlist;
| 5 | root | localhost | test | Query | 160 | Waiting for release of readlock | INSERT INTO foo VALUES ( ‘tata2′) |
Conclusion
The first method seems more adapted when transferring data from one MySQL server to another, while the second plutût it will be used in case of a backup cold
How to modify column definition in MySQL
November 22nd, 2010assume we want to change column named address from varchar(1000) to varchar(512) for table employee
alter table employee modify address varchar(512)
it will modify the column length from 1000 to varchar(512)
How to Change Innodb Log File
November 19th, 2010How to Change Innodb Log File
Step 1
Have a clean MySQL Server Stop , check error log for any error.
Step 2
change value of innodb_log_file_size in my.cnf file as you want it
Step 3
Move/rename the log file sizes ib_log* .
Step 4
Restart MySQL Server.
Step 5
Check MySQL error log for any error .
Step 6
Check Innodb Log File size it should be changed
Upgrading to MariaDB from MySQL 5.0 (or older version)
November 19th, 2010If you upgrade to MariaDB 5.1 from MySQL 5.1 you don’t have to do anything with your data or MySQL clients. Things should “just work”.
When upgrading between different major versions of MariaDB or MySQL you need to run the mysql_upgrade program to convert data that are incompatible between versions. This will also update your privilege tables in the mysql database to the latest format.
In almost all cases mysql_upgrade should be able to convert your tables, without you having to dump and restore your data.
After installing MariaDB, just do:
mysql_upgrade –verbose
If you want to run with a specific TCP/IP port do:
mysql_upgrade –host=127.0.0.1 –port=3308 –protocol=tcp
If you want to connect with a socket do:
mysql_upgrade –socket=127.0.0.1 –protocol=socket
To see other options, use –help.
“mysql_upgrade” reads the my.cnf sections [mysql_upgrade] and [client] for default values.
There are a variety of reasons tables need to be converted; they could be any of the following:
* The collation (sorting order) for an index column has changed
* A field type has changed storage format
o DECIMAL and VARCHAR changed format between MySQL 4.1 and MySQL 5.0
* An engine has a new storage format
o ARCHIVE changed storage format between 5.0 and 5.1
* The format for storing table names has changed
o In MySQL 5.1 table names are encoded so that the file names are identical on all computers. Old table names that contains forbidden file name characters will show up prefixed with #mysql50# in ‘SHOW TABLES’ until you convert them.
If you don’t convert the tables, one of the following things may happen:
* You will get warnings in the error log every time you access a table with an invalid (old) file name.
* When searching on key values you may not find all rows
* You will get an error “ERROR 1459 (HY000): Table upgrade required” when accessing the table.
* You may get crashes
“mysql_upgrade” works by calling mysqlcheck with different options and running the “mysql_fix_privileges” script. If you have trouble with “mysql_upgrade”, you can run these commands separately to get more information of what is going on.
Most of the things in the MySQL 5.1 manual section also applies to MariaDB.
The following differences exists between “mysql_upgrade” in MariaDB and MySQL (as of MariaDB 5.1.50):
* MariaDB will convert long table names properly.
* MariaDB will convert InnoDB tables (no need to do a dump/restore or ALTER TABLE).
* MariaDB will convert old archive tables to the new 5.1 format (note: new feature in testing).
* “mysql_upgrade –verbose” will run “mysqlcheck –verbose” so that you get more information of what is happening.
mysql_upgrade
Actions
* History
* Flag as Spam/Inappropriate
User
monty
Created
2 months, 2 weeks ago
Modified
2 months, 1 week ago
Type
article
Status
active
mysql_upgrade is a tool that checks and updates your tables to the latest version.
Usage:
mysql_upgrade [--force] [--user=# --password --host=hostname --port=# --socket=#
--protocol=tcp|socket|pipe|memory --verbose] OTHER_OPTIONS]
You should run mysql_upgrade when you upgrade from another major MySQL/MariaDB release, like from MySQL 5.0 to MariaDB 5.1.
The following groups are read from the my.cnf files: [mysql_upgrade] and [client].
The following options to handle option files may be given as the first argument:
–print-defaults Print the program argument list and exit.
–no-defaults Don’t read default options from any option file.
–defaults-file=# Only read default options from the given file #.
–defaults-extra-file=# Read this file after the global files are read.
Main arguments are:
-?, –help Display this help message and exit.
-#, –debug[=name] Output debug log.
–debug-check Check memory and open file usage at exit.
-T, –debug-info Print some debug info at exit.
-f, –force Force execution of mysqlcheck even if mysql_upgrade has already been executed for the current version of MySQL.
-h, –host=name Connect to host.
-p, –password[=name] Password to use when connecting to server. If password is not given, it’s solicited on the tty.
-P, –port=name Port number to use for connection or 0 for default to, in order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306).
–protocol=name The protocol to use for connection (tcp, socket, pipe, memory).
-s, –silent Print less information.
-S, –socket=name The socket file to use for connection.
-t, –tmpdir=name Directory for temporary files.
-u, –user=name User for login if not current user.
-v, –verbose Display more output about the process.
–write-binlog All commands including mysqlcheck are binlogged. Enabled by default ; Use –skip-write-binlog when commands should not be sent to replication slaves.
“mysql_upgrade” is mainly a framework to call mysqlcheck. mysql_upgrade works by doing the following operations:
# Find out path to datadir
echo “show show variables like ‘datadir’” | mysql
mysqlcheck –no-defaults –all-databases –fix-db-names –fix-table-names –write-binlog
mysqlcheck –check-upgrade –all-databases –auto-repair –write-binlog
mysql_fix_privilege_tables
The connect options given to mysql_upgrade are passed along to mysqlcheck and mysql.
The mysql_fix_privilege_tables script is not actually called; it’s included as part of mysql_upgrade.
If you have a problem with mysql_upgrade, try run it in very verbose mode:
mysql_upgrade –verbose –verbose other-options
The following differences exists between “mysql_upgrade” in MariaDB and MySQL (as of MariaDB 5.1.50):
* MariaDB will convert long table names properly.
* MariaDB will convert InnoDB tables (no need to do a dump/restore or ALTER TABLE).
* MariaDB will convert old archive tables to the new 5.1 format (note: new feature in testing).
* “mysql_upgrade –verbose” will run “mysqlcheck –verbose” so that you get more information of what is happening.
* More descriptive output.
How to fix corrrupted relay log
November 13th, 2010When we get this type of error and MySQL replicaton stops
Last_Error: Could not parse relay log event entry. The possible reasons are: the master’s binary log is corrupted (you can check this by running ‘mysqlbinlog’ on the binary log), the slave’s relay log is corrupted (you can check this by running ‘mysqlbinlog’ on the relay log), a network problem, or a bug in the master’s or slave’s MySQL code. If you want to check the master’s binary log or slave’s relay log, you will be able to know their names by issuing ‘SHOW SLAVE STATUS’ on this slave.
Possible reasons of the relay log corruption
1) Network
2) MySQL bugs on master/Slave
3) Hardware problems
4) max_allowed_packet value reset without restarting the replication
Solution:
Run show slave status and get these values Relay_Master_Log_File and Exec_Master_Log_Pos, Now reset the replication on slave
CHANGE master TO master_log_file=Relay_Master_Log_File,master_log_pos=Exec_Master_Log_Pos ;
Start Slave ;
we are good to go, issue should be fixed now.for this to work Relay_Master_Log_File file must exist on master.
