MySQL Basics

The main configuration file of MySQL is my.cnf. The full path to the configuration file is /etc/my.cnf.
The main files of these database are normally located in /var/lib/mysql directory.

Basically one can see three types of files .frm, .myi, .myd.. But they are not same for all tables and db. They differ based on the engines you use and sometimes even differ with the os. There are lots of other factors that is in the backend behind the type of files you see. We will  see some basic differences.

.FRM  =>  It has the table structure of your table or table definition

.MYI  =>   It has the indexes of your table

.MYD =>   It contains your data

For ex: if your db name is school and tables called class and student. The Physical structure will have a directory called school and files class.frm, class.myi, class.myd, student.frm, student.myi, student.myd.

Engines Specific:

 

Lets consider table student belongs to innodb and table class has MyIsam

 

Innodb:

      Innodb has only .frm files and it has its own tablespace where it contains indexes and datas and its shared in databases. for ex: student.frm

 

MyISAM:

      MyIsam has all the three files. where .myi has your indexes, .myd has your table datas and .frm has its table definition. 

ex: class.frm, class.myi, class.myd

 

Need this:

 

   You can use these files when your db crash, or when you upgrade your db to another version and it can also be used while migrating and repairing your indexes without affecting data..

 

Bits and pieces

 

   This is for those who are not aware about engines. Apart from Innodb and MyIsam there are also some other engines in mysql such as Merge, Memory, Cluster etc..

Read more »

Backend commands to get Wordpress Details

Please select the appropriate database and enter the MySQL prompt. You can get the details of the database by checking the wp-config file.

select * from wp_options where `option_name` = 'siteurl';
select * from wp_options where `option_name` = 'home';
SELECT * FROM `wp_options` WHERE `option_name` = 'current_theme';
UPDATE `eugi12_wrdp1`.`wp_users` SET `user_pass` = MD5( '12345678' ) WHERE `wp_users`.`ID` =1;

Find the version of Wordpress : grep wp_version wp-includes/version.php

Read more »

cPanel Fantastico Install Error : Solved (Warning: mysql_connect() [function.mysql-connect]: Access denied for user @'localhost' (using password: YES) in /tmp/cpanel_phpengine)

I encountered with this error while trying to install Drupal via fantastico in cPanel. This error replicates for wordpress or any other 3rd party application which you try to install via fantastico.

The reason for this error is the password mismatch of cpanel and mysql. To fix this error login to cPanel and take change password under Preferences and change your password to a new one. Make sure that you check the box "Allow MySQL password change" as shown below:

Now retry the installation and this will work. :)

Read more »

Installing LAMP on Linux --> The easy way

What is LAMP? Its actually a short form the expansion of LAMP is
L --> Linux
A --> Apache
M --> Mysql
P --> Php {{ P can be perl or python too }}
These are the basic things which are needed for a web server to work. The steps for installation are for red hat flavoured Linux systems or servers.
Login as root to the server where LAMP is to be installed.

• APACHE

# yum install httpd httpd-devel
# /etc/init.d/httpd start
Now the apache will be installed and its running. You can verify the installation by giving the command.
# telnet localhost 80 which should give the output silimilar to this
--------------------------------------------------------------
telnet localhost 80
Trying 127.0.0.1...
Connected to localhost (127.0.0.1).
Escape character is '^]'.
--------------------------------------------------------------
To configure apache you will have to edit the configuration file /etc/httpd/conf/httpd.conf. Please make sure to restart apache after editing configuration file for changes to take effect.
#/etc/init.d/httpd restart

• MYSQL

# yum install mysql mysql-server mysql-devel
# /etc/init.d/mysqld start
Now Mysql is started. We will perform a quick basic setup of mysql now.
Note: For fedora 16/17 users
systemctl start mysqld.service
systemctl enable mysqld.service
# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 349034 to server version: 4.1.22-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
Set the mysql root password now:
mysql>USE mysql;
(Replace 'yourpass with your required password' in the command below)
mysql> UPDATE user SET Password=PASSWORD('yourpass') WHERE user='root';
mysql> FLUSH PRIVILEGES;
mysql> quit
Bye
Now to login with new details:
mysql -u root -p
Enter Password:

• PHP

# yum install php php-mysql php-common php-gd php-mbstring php-mcrypt php-devel php-xml
# /etc/init.d/httpd restart
To verify the php installation type the command below:
# php -v
We can also create a php info page to test this.

Read more »

Repairing Database and Tables

MyISAM was the default storage engine for the MySQL. It is based on the older ISAM code but has many useful extensions. The major deficiency of MyISAM is the absence of transactions support. Versions of MySQL 5.5 and greater have switched to the InnoDB engine to ensure referential integrity constraints, and higher concurrency.

Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. MySQL uses a .frm file to store the definition of the table, but this file is not a part of the MyISAM engine; instead it is a part of the server. The data file has a .MYD (MYData) extension. The index file has a .MYI (MYIndex) extension.

I will now show how to repair the corrupted databases and tables. Usually the mysql files are stored in /var/lib/mysql. Hence navigate your way to the this location.


$ cd /var/lib/mysql
You can find all the databases in the server as folders in this location and enter the folder of the corrupted database.
$ cd db_name
To check and automatically repair all tables
$  myisamchk --silent --force --fast --update-state *.MYI.

If you only want to repair a specific table give the command below:
$ myisamchk -r table_name.MYI

Read more »

Adding Users in Mysql and Granting Privileges

You can add users to this database and specify the databases to which they will have access with the grant command, which has the syntax.

sql> grant all privileges on database.* to username@"servername" identified by 'password';

Example:
sql> grant all privileges on data1.* to dbuser@"localhost" identified by 'secdat';

Where data1 is the database name
dbuser is the user name who has to access the db / name of new user.
secdat is the password

If you want to restrict that user from full privilege on a db you can use the following syntax:

sql> grant CREATE,INSERT,DELETE,UPDATE,SELECT on data1.* to dbuser2@localhost;

Read more »

Install LAMP and phpMyAdmin on Ubuntu 11.10 Easy Way

Open terminal and type the command given below. Please note that we have to give "^" at the end and its not a typo. Many prompts will pop up during installation to set password or asking for the server password. Please give them when asked for.
sudo apt-get install lamp-server^

After Installation open a web browser and enter the address http://localhost/.

You should see a page that says "It Works!" Hence you can confirm that apache is working perfectly.

Create a file in the /var/www directory called info.php. Enter the following command in the terminal to create the file.
echo "" | sudo tee /var/www/info.php

Now open a web browser again and enter the address http://localhost/info.php . You will get a php info page. If you are not getting this page try restarting apache2 by giving the command below.
/etc/init.d/apache2 restart

INSTALLING PHPMYADMIN.

LAMP installation will not install phpmyadmin. Also phpmyadmin is not required for the proper functioning of LAMP. It just gives an easy platform for editing.
sudo apt-get install libapache2-mod-auth-mysql phpmyadmin

Open your web browser and enter the address http://localhost/phpmyadmin/ to access it.

Read more »

Change / Reset MYSQL password.

Step # 1 : Stop MySQL Server:

# /etc/init.d/mysql stop

Output:
Stopping MySQL database server: mysqld.

Step # 2: Start to MySQL server w/o password:

# mysqld_safe --skip-grant-tables &

Output:
[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started

Step # 3: Connect to mysql server using mysql client:

# mysql -u root

Output:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

Step # 4: Setup New MYSQL Password:

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit


Step # 5: Stop MySQL Server:

# /etc/init.d/mysql stop

Output:
Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended
[1]+  Done                    mysqld_safe --skip-grant-tables

Step # 6: Start MySQL Server:

# /etc/init.d/mysql start
# mysql -u root -p

Read more »