Board index Linux Mysql

Moderator: chandranjoy

MySQL General Stuffs

Postby chandranjoy » Mon Mar 01, 2010 5:26 am

1. How to set the MySQL root (superuser) password
#mysqladmin -u root password ‘new-password’
#mysqladmin -u root -h linux2 password ‘new-password’

***Note: -h denotes here your local hostname of your server.***

Or use this method to set root password for MqSQL
Stay connected to MySQL and enter:
DELETE FROM mysql.user WHERE User = ”;
SELECT Host, User FROM mysql.user;

Look for the record that has root in the User column and something other
than localhost in the Host column. This is the host_name.
SET PASSWORD FOR ‘root’@'localhost’ = PASSWORD(‘new_password’);
SET PASSWORD FOR ‘root’@'host_name’ = PASSWORD(‘new_password’);

Remember, this is the MySQL superuser for all databases

2. Nullifying MySQL root password:
Recover MySQL root Password

You can recover MySQL database server password with following five easy steps.

Step # 1: Stop the MySQL server process.

Step # 2: Start the MySQL (mysqld) server/daemon process with the --skip-grant-tables option so that it will not prompt for password.

Step # 3: Connect to mysql server as the root user.

Step # 4: Setup new mysql root account password.

Step # 5:
Exit and restart the MySQL server.

Here are commands you need to type for each step (login as the root user):
Step # 1 : Stop mysql service

# /etc/init.d/mysql stop


Stopping MySQL database server: mysqld.

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

# mysqld_safe --skip-grant-tables &


[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


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.


Step # 4: Setup new MySQL root user 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


Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/
mysqld_safe[6186]: ended

[1]+ Done mysqld_safe --skip-grant-tables

Step # 6: Start MySQL server and test it
# /etc/init.d/mysql start
# mysql -u root -p

3. Major Differences between “MyISAM & InnoDB” (Table Storage Structures)

* MyISAM is the default table type in MySQL.It provides a simple tree
structure for
quick indexing on small tables. InnoDB table, the one which is best for
huge tables.It’s fast and effecient, but the trees are pretty large, so
it’s not good for smaller tables.

* MyISAM is the default table type in MySQL, the data is stored in three
files, name.frm for table definition, name.myd for the data and name.myi
for the indexing. InnoDB is for transactional operations (for example used
in banking to “queue” queries and update the table with all the changes at
once (commit)) as well as rollback and crash recovery.

* One more difference between MyISAM and InnoDB is that InnoDB supports
transaction. You can commit and rollback with InnoDB but with MyISAM once
you issue a command it’s done.

* MyISAM does not support foreign keys where as InnoDB supports.

* Locking: MyISAM provide the table level locking means if the data in one
table has been modified by the other table,the entire table will lock for
the next process.But INNODB provide the row level locking only the row of
the table that is being updated is locked.

* Data Types: MyISAM provide a full variety of datatypes,but INNODB doesn’t.

* Memory: MyISAM occupies less memory sapce for tables rather than InnoDB.

4. MySQL Database Backup and Restore:

You can use mysqldump to create a simple backup of your database using the
following syntax.
mysqldump -u [username] -p [databasename] > [backupfile.sql]

o -u – this is your database username
o -p – this is the password for your database
o [databasename] – the name of your database
o [backupfile.sql] – the file to which the backup should be written.
#mysqldump -u act -p actdb > actdbbak.sql

***Note: It will prompt you the password for the user act***

For restoring the database,
mysql -u [username] -p [databasename] < [backupfile.sql]

#mysql -u act -p actdb < actdbbak.sql

***Note: It will aprompt you the password for the user act***

Backing up only specified tables:

mysqldump -u [username] -p [databasename] [table1 table2 ....] > db_backup_name

If you’d like restrict the backup to only certain tables of your database,
you can also specify the tables you want to backup. Let’s say that you
want to backup only customer_master & customer_details from the Customers
database, you do that by issuing

Here i dropped table1 and table2,

mysqldump –add-drop-table -u act -p actdb table1 table2 > custback.sql

If you need to restore a database that already exists, you’ll need to use
mysqlimport command. The
syntax for mysqlimport is as follows:

mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql]

5. Backing Up and Restoring using PHPMyAdmin:

To backup your MySQL database using PHPMyAdmin just follow a couple of
* Open phpMyAdmin.
* Select your database by clicking the database name in the list on
the left of the screen.
* Click the Export link. This should bring up a new screen that says
View dump of database (or something similar).
* In the Export area, click the Select All link to choose all of the
tables in your database.
* In the SQL options area, click the right options.
* Click on the Save as file option and the corresponding compression
option and then click the ‘Go’ button. A dialog box should appear
prompting you to save the file locally.

Restoring your database is easy as well as backing it up. Make the following:

* Open phpMyAdmin.
* Create an appropriately named database and select it by clicking the
database name in the list on the left of the screen. If you would like
to rewrite the backup over an existing database then click on the
database name, select all the check boxes next to the table names and
select Drop to delete all existing tables in the database.
* Click the SQL link. This should bring up a new screen where you can
either type in SQL commands, or upload your SQL file.
* Use the browse button to find the database file.
* Click Go button. This will upload the backup, execute the SQL
commands and re-create your database.

6.MySQL Commands:

To login (from unix shell) use -h only if needed.
# [mysql dir]/bin/mysql -h hostname -u root -p

Create a database on the sql server.
Code: Select all
mysql> create database [databasename];

List all databases on the sql server.
mysql> show databases;

Switch to a database.
mysql> use [db name];

To see all the tables in the db.
mysql> show tables;

To see database’s field formats.
mysql> describe [table name];

To delete a db.
mysql> drop database [database name];

To delete a table.
mysql> drop table [table name];

Show all data in a table.
mysql> SELECT * FROM [table name];

Returns the columns and column information pertaining to the designated table.
mysql> show columns from [table name];

Show certain selected rows with the value “whatever”.
mysql> SELECT * FROM [table name] WHERE [field name] = “whatever”;

Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES(‘%’,'username’,PASSWORD(‘password’));
mysql> flush privileges;

Change a users password from unix shell.

# [mysql dir]/bin/mysqladmin -u username -h -p password ‘new-password’

Change a users password from MySQL prompt. Login as root. Set the password. Update privs.

# mysql -u root -p
mysql> SET PASSWORD FOR ‘user’@'hostname’ = PASSWORD(‘passwordhere’);
mysql> flush privileges;

Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.

# /etc/init.d/mysql stop
# mysqld_safe –skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD(“newrootpassword”) where User=’root’;
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

Set a root password if there is on root password.
# mysqladmin -u root password newpassword

Update a root password.
# mysqladmin -u root -p oldpassword newpassword

Allow the user “bob” to connect to the server from localhost using the password “passwd”. Login as root. Switch to the MySQL db. Give privs. Update privs.
# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by ‘passwd’;
mysql> flush privileges;

Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.
# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES (‘%’,'databasename’,'username’,'Y’,'Y’,'Y’,'Y’,'Y’,'N’);
mysql> flush privileges;


mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

mysql> DELETE from [table name] where [field name] = ‘whatever’;
Update database permissions/privilages

mysql> flush privileges;
Delete a column

mysql> alter table [table name] drop column [column name];.

# [mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql

Dump one database for backup.
# [mysql dir]/bin/mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql

Dump a table from a database.
# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.
# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

Create Table Example 1.
mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));
Create Table Example 2.

mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default ‘bato’);

Enjoy Folks.. :)

Remote access to mysql database

Step 1 :

vi /etc/my.cnf

pid-file = /var/run/mysqld/
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/English
bind-address = mysql server ip
# skip-networking


bind-address :
IP address to bind to.

skip-networking :
Don’t listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should removed from file or put it in comment state.

Step 2 :
/etc/init.d/mysql restart

Step 3 : Grant access to remote IP address

mysql -u root -p mysqlGrant access to new database

Step 4 : If you want to add new database aaa for user bbb and remote IP then type following commands at mysql prompt:

Step 5 :
Grant access to existing database
mysql> update db set Host=’’ where Db=’webdb’;
mysql> update user set Host=’’ where user=’webadmin’;
mysql> exit

Step 6 :
Test it From remote system
mysql -u webadmin –h –p

Enjoy .... :)
Site Admin
Posts: 283
Joined: Fri Oct 23, 2009 11:19 pm

Return to Mysql

Who is online

Users browsing this forum: No registered users and 1 guest