Thursday, August 8, 2013

Setup mysql server

1.Add permission to tmp folder.
 chown root:root /tmp
  107  chmod 1777 /tmp
2.Video guide:
3.Command install
sudo yum install mysql-server
service mysqld start
sudo /usr/bin/mysql_secure_installation
Open port in iptables
-I INPUT -p tcp --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
-I OUTPUT -p tcp --sport 3306 -m state --state ESTABLISHED -j ACCEPT
To get a list of MySQL users:

mysql> select user,host from mysql.user;

To find the privilege(s) granted to a particular MySQL account:

mysql> show grants for ‘root’@'%’;

4.Reset password root:

/etc/init.d/mysqld 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/mysqld stop
/etc/init.d/mysqld start
5.Remove mysql
tep 1: Uninstall existing rpms using yum


# yum remove mysql mysql-server
Step 2: Remove or move /var/lib/mysql folder.

# mv /var/lib/mysql /var/lib/mysql-bak
Step 3: Reinstall mysql again using yum.


# yum install mysql mysql-server
Step 4: Now mysql service will start successfully.
6.Mysql command
Add a database user
To make the user run the following in the mysql shell:

INSERT INTO mysql.user (User,Host,Password) VALUES('demouser','localhost',PASSWORD('demopassword'));
or
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
When you make changes to the user table in the mysql database you need to tell MySQL to read the changes by flushing the privileges. To wit:

FLUSH PRIVILEGES;

Grant database user permissions

Right now our new user has no privileges. It can be used to log on, but it can't be used to make any database changes. Let's give it full permissions for our new database by running:

GRANT ALL PRIVILEGES ON demodb.* to demouser@localhost;
And follow it up with the usual:

FLUSH PRIVILEGES;

To provide a specific user with a permission, you can use this framework:
 GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;

If you want to give them access to any database or to any table, make sure to put an asterisk (*) in the place of the database name or table name. 

Each time you update or change a permission be sure to use the Flush Privileges command.

If you need to revoke a permission, the structure is almost identical to granting it:
 REVOKE [type of permission] ON [database name].[table name] TO ‘[username]’@‘localhost’;

Just as you can delete databases with DROP, you can use DROP to delete a user altogether:
 DROP USER ‘demo’@‘localhost’;

To test out your new user, log out by typing
 quit 

MySQL Import Command 

he syntax is as follows to import the data created by mysqldump command:
 
mysql -u {DB-USER-NAME} -p {DB-NAME} < {db.file.sql}
mysql -u {DB-USER-NAME} -h {MySQL-SERVER-HOST-NAME} -p {DB-NAME} < {db.file.sql}
 
In this example import a file called sales.sql for salesdb1 user and sales db, enter: $ mysql -u sales -p salesdb1 < sales.sql If database salesdb1 does not exists, first create it and than import it as follows: $ mysql -u root -p -e 'create database salesdb1' $ mysql -u sales -p salesdb1 < sales.sql
Install wordpress
First, let's make the database (I'm calling mine wordpress for 
simplicity's sake; feel free to give it whatever name you choose):

CREATE DATABASE wordpress;
Query OK, 1 row affected (0.00 sec)


Then we need to create the new user. You can replace the database, name, and password, with whatever you prefer:

CREATE USER wordpressuser@localhost;
Query OK, 0 rows affected (0.00 sec)



Set the password for your new user:

SET PASSWORD FOR wordpressuser@localhost= PASSWORD("password");
Query OK, 0 rows affected (0.00 sec)



Finish up by granting all privileges to the new user. Without this 
command, the wordpress installer will not be able to start up:


GRANT ALL PRIVILEGES ON wordpress.* TO wordpressuser@localhost IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)


Then refresh MySQL:

FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)


Exit out of the MySQL shell:

exit