Tech Chorus

On 11 October, 2009 we, the members of the Bangalore MySQL User Group had a meeting in the Binary Vibes office. We had an informal talk on various topics. Overall, it was a good meeting. My presentation - MySQL Naming Conventions went well.

MySQL has a query logging feature. In order to use it you have to first enable it.

Enabling the general query log

Step 1: Set your log file in /etc/my.cnf

vi /etc/my.cnf

In the [mysqld] section specify the general log file name:

log=/var/log/mysqld.general.log

Step 2: Create the file and make sure it is owned by the system user mysql

touch /var/log/mysqld.general.log
chown mysql.mysql /var/log/mysqld.general.lo

We discussed how to install LAMP in one of our previous articles. Some of you may be wondering how to create databases, users and tables on your newly installed MySQL server. This blog post introduces the preliminary steps to work with your new LAMP server.

We are going to learn the following:

• Changing MySQL user password
• Creating databases
• Deleting databases
• Creating MySQL user accounts
• Deleting MySQL user accounts
• Connecting to the MySQL server from the command line

While developing applications creating test data can be a tedious process to the developers. You don't have to do the same things like creating a user for testing over and over again.

Use a data generator. There are numerous tools freely available online that can provide the test data for you.

Do you want to export data stored in a MySQL database to CSV file?

The solution is damn easy if you already know how to connect to MySQL database and read or display data from a PHP script. Let's start working on it.

Let us first create a sample table. In our example scenario, let us create a table to store contact information.

CREATE TABLE `contacts` (
`first_name` VARCHAR( 50 ) NOT NULL ,
`middle_name` VARCHAR( 50 ) NOT NULL ,
`last_name` VARCHAR( 50 ) NOT NULL ,
`email` VARCHAR( 320 ) NOT NULL ,
`phone` VARCHAR( 20 ) NOT NULL
) ENGINE = MYISAM;

Moving MySQL Database To A Different Computer AKA Backup And Restore

Many popular web applications are written in PHP and use MySQL database. In this post I will describe, how you can move your database to a different computer. Almost all web applications store database and files system information in configuration files. You can actually move your web application to a different server without much hassle by editing few lines in these configuration files. I will write more about the configuration files of web applications in a different post. In this post I will walk you through the steps to move your MySQL database to a different server. The commands will help you move both data and schema.

What do you need to know before moving a database? On both the computers you will require the

• MySQL database name
• MySQL username that has access to the database
• Password for the username that has access to database

You can take a backup of your database in the form of SQL dump and restore it on another server quickly from the Shell. Later in the post we will discuss how you can import and export MySQL databases using a graphical tool.

Launch the terminal. If the server is accessible remotely using SSH you can execute these commands on the secure shell.

Command 1 to generate the backup

mysqldump -u mydatabaseuser -p mydatabase > backup.sql

When choosing the MySQL field type to store dates, date is the preferred type. MySQL stores dates in the format yyyy-mm-dd which is seldom used to print dates on web pages. As a PHP developer you will eventually come across a need to convert the MySQL date format into dd-mm-yyyy or mm-dd-yyyy or another format. This post tells you how you can do that in your PHP programs.

Let us create a hypothetical situation to demonstrate the program.

Create the MySQL table.

CREATE TABLE dates (
  name varchar(30),
  birthday date 
) 

We discussed how to install LAMP in one of our previous articles. Some of you may be wondering how to create databases, users and tables on your newly installed MySQL server. This blog post introduces the preliminary steps to work with your new LAMP server.

We are going to learn the following:

• Changing MySQL user password
• Creating databases
• Deleting databases
• Creating MySQL user accounts
• Deleting MySQL user accounts