MySQL Import Command

How do I import data stored in a “.sql” file (created by mysqldump command) under UNIX or Linux operating systems?

The syntax is as follows to import the data created by mysqldump command:

mysql -u{DB-USER-NAME} -p{DB-PASSWORD} {DB-NAME} < {db.file.sql}
mysql -h{MySQL-SERVER-HOST-NAME} -u{DB-USER-NAME} -p{DB-PASSWORD} {DB-NAME} < {db.file.sql}


In this example import a file called sales.sql for salesdb1 user and sales db, enter:
$ mysql -u123tweak -p123password 123tweak < 123tweak.sql
Read more…

Reset MySQL root Password

To reset MySQL root user’s password, follow the following steps.

1. Terminate all running instances of MySQL server. You can do that either via the RC script (/etc/init.d/<script_name> stop or /user/local/etc/rc.d/<script_name> stop for FreeBSD) or using the tools like kill and pkill. To use kill or pkill, you will have to use ps or pgrep to find out the process names or process ID (PID).
   

   Using the RC script to do a proper shutdown is safer than using kill or pkill.

2. Start the MySQL server in safe modemysqld_safe --skip-grant-tables --user=mysql
3. On another terminal, login to the MySQL server as the root usermysql -u root
   Read more…

Turn on MySQL query cache to speed up query performance?

Many times developers looking for ways to speed up query, in mysql we can enable query cache to speed up query performance. Whenever query cache is enable, it will cache the query in memory and boost query performance.

As we know, speed is always the most important element in developing a website especially for those high traffic database driven website. You can try to turn on query cache to speed up query.

To speed up query, enable the MySQL query cache, before that you need to set few variables in mysql configuration file (usually is /etc/my.cnf or my.ini)

- 1st, set query_cache_type to 1. (There are 3 possible settings: 0 (disable / off), 1 (enable / on) and 2 (on demand).

query-cache-type = 1
Read more…

Backup MySQL in command line with compression

For those who looking for way to backup mysql database,
you can use mysqldump to backup mysql database.

Below is the script example to backup mysql database in command line:-

$ mysqldump -h localhost -u username -p password database_name > backup_db.sql

If your mysql database is very big, you might want to compress your sql file.
Just use the mysql backup command below and pipe the output to gzip,
then you will get the output as gzip file.
Read more…

How to install MySQL on CentOS Redhat Linux

Install MySQL with YUM
yum install mysql-server mysql php-mysql

How to configure MySQL Read more…

MySQL IF() usage

This article discusses the usage of the MySQL IF() function to display the status of a BOOLEAN or TINYINT(1) column. While working on a tool to track a migration, I needed a means to show the migration status of a collection of tasks. If all the tasks had been migrated, then I needed to summarize that collection as being “migrated”. Where as, if any one task was incomplete, all tasks in that collection needed to be summarized as being “not migrated”.

This particular project was to make changes to collections of templates that were grouped by a server. To do this, I used the MySQL IF() function to check the number of templates in the collection was equal to the summation of the boolean flag associated with each template. When a template was migrated, it’s BOOLEAN flag was set to 1.
Read more…

Reset lost root password in MySQL

This is a handy procedure that can be used to reset the root password for the MySQL account. I had to use this procedure on a failed installation. It appeared that I had a problem with the bin/install_db command, and it failed to pre-populate the mysql.user table with the correct initial permissions and grants. Without this information, I was dead in the water. I had an empty grant / access table so I couldn’t log in.

The following sequence was taken to correct this problem: Read more…

Export MySQL data into CSV or PSV files

One way to export data from MySQL tables into plain text files would be to use mysqldump, then remove SQL from the dump file. Perhaps an easier way is to pipe SQL SELECT output to a file.

For CSV (comma delimited, enclosing content in quotes):

mysql mydb -e "SELECT * FROM my_table" | sed 's/\t/","/g;s/^/"/;s/$/"/;' > my_db.my_table.csv

Example output:

"column1","column2"
"value1","value2"
Read more…

How to set up master-slave replication in MySQL

Before we go into how to set up master-slave replication in MySQL, let us talk about some of the reasons I have set up master-slave replication using MySQL.

1) Offload some of the queries from one server to another and spread the load: One of the biggest advantages to have master-slave set up in MySQL is to be able to use master for all of the inserts and send some, if not all, select queries to slave. This will most probably speed up your application without having to diving into optimizing all the queries or buying more hardware.

2) Do backups from slave: One of the advantages people overlook is that you can use MySQL slave to do backups from. That way site is not affected at all when doing backups. This becomes a big deal when your database has grown to multiple gigs and every time you do backups using mysqldump, site lags when table locks happen. For some sites, this could mean that site goes down for few secs to minutes. If you have slave, you just take slave out of rotation (should be built into code) and run backups off the slave. You can even stop slave MySQL instance and copy the var folder instead of doing mysqldump.
Read more…

Calculate Mysql memory

MySQL memory = key_buffer + max_connections * (join_buffer + record_buffer + sort_buffer + thread_stack + tmp_table_size)

OPTIMISING MYSQL

To obtain the stat of your mysql server since it has been loaded, run mysqladmin processlist extended-status as mentionned above.
Read more…

Next Page »