• Mysql Startup Tutorial

    Mysql Startup Tutorial

    Common Required Packages

    Following RPM are required to setup a normal Mysql database server.

    1. mysql-server
    2. mysql
    3. php-mysql (if php is going to access mysql)

    Use rpm command to install these packages.

    Eg:

    [root@tmp server]# rpm -ivh mysql-server-5.0.77-4.el5_4.2

    Configuration file

    /etc/my.cnf

    The /etc/my.cnf file is the main MySQL configuration file. It sets the default MySQL database location and other parameters.

    Port

    Mysql default port is 3306

    Setting up root password
    First Time

    mysqladmin -uroot password $MYSQLPW

    MySQL stores all its username and password data in a special database named mysql. You can add users to this database and specify the databases to which they will have access with the grant command. The MySQL root or superuser account, which is used to create and delete databases, is the exception. You need to use the mysqladmin command to set your root password. Only two steps are necessary for a brand new MySQL installation.

    1. Make sure MySQL is started.
    2. Use the mysqladmin command to set the MySQL root password. The syntax is as follows:

    [root@tmp server]# mysqladmin -u root password new-password

    Reset Root Password on running Mysql

    1. Stop the Mysql

    [root@localhost ~]# /etc/init.d/mysqld stop

    2. Start again with mysqlsafe

    [root@localhost ~]# mysqld_safe --skip-grant-tables &

    3. From another terminal,

    [root@localhost ~]# mysql -u root

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

    In the mysql database, there is one table named as "user" in that table set password for the user root.

    4. Restart Mysql

    [root@localhost ~]# /etc/init.d/mysqld restart

    Accessing The MySQL Command Line

    MySQL command line interpreter (CLI) can be accessed as follows.

    [root@localhost ~]# mysql -u root -p
    Enter password:
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 53
    Server version: 5.0.77 Source distribution

    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

    mysql>

    Basic mysql tasks in CLI

    1. List the databases in the server.

    mysql> show databases;

    2. Create a database

    mysql> create database mynewdatabasename;

    3. Delete a databse(drop)

    mysql> drop database unwanteddatabasename;

    4. create a user

    mysql> create user newusername;

    5. List the users

    mysql> select user from mysql.user;

    6. Providing privilages to one user on one database

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

    mysql> flush privileges;

    The flush privileges is used to write the privilege changes to the mysql.sql database

    7. Delete a user

    mysql> drop user test;

    Backup a Database

    We can use mysqldump for backup a mysql database.

    [root@localhost ~]# mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]

    Eg:

    [root@localhost ~]# mysqldump -u root -p mysecret mydatabase > mydatabasebackup.sql

    Using this method, we can create a backup as sql file. If the case is a huge database, we can use the standard pipes to dump and zip.

    Eg:

    mysqldump -uroot -pmysecret --database mybigdatabase | gzip -c > mybigdatabase.sql.gz;

    In Some time, putting the password in command will cause security problems. In such case, we can use mysqldump in the following manner.

    [root@localhost ~]# mysqldump -u root -p database > database.sql

    in this case we will be asked for the root password when the command executed.

    Restore a Database

    [root@localhost ~]# mysql -u user -p 'password' db-name < db-name.sql

    If the password needs to be secured,

    [root@localhost ~]# mysql -u user -p db-name < db-name.sql

    Ref: http://nixcraft.com

0 comments:

Leave a Reply

.

FEEDS FROM THIS BLOG

FEEDJIT Live Traffic Feed

My Zimbio   Blog Directory - OnToplist.com   Red Hat Enterprice Linux RHCE RHCT RHCA RHCSS  Powered by  MyPagerank.Net  Academic blogs & blog posts   BlogRankers.com  TopOfBlogs  BuzzCritic     Academics  Blog Directory  Hihera.com  Technology Blogs - Blog Rankings   Visit blogadda.com to discover Indian blogs  Academics Blogs  Computers (Linux) - TOP.ORG    Rallyshare    Yahoo bot last visit powered by MyPagerank.Net     The Weblog Review  blogarama - the blog directory  SEO services    Academic blogs & blog posts