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
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
