|
Database HowTo's -
MySQL HowTo's
|
|
Written by Allen Sanabria
|
|
Sunday, 10 February 2008 00:16 |
|
Mysql storing encrypted passwords howto, All these commands were tested on MySQL5
-
This example we will create a MD5 encrypted password
- We need to create a table called user_md5, (The value is returned as a binary string of 32 hex digits)
"CREATE TABLE user_md5 (user_name VARCHAR(16), password VARCHAR(32));"
Query OK, 0 rows affected (0.10 sec)
- Now we need to insert a user into the table
"INSERT INTO user_md5 VALUES ('test',MD5('test') );"
Query OK, 1 row affected (0.00 sec)
- Now lets see if we query the user with the unencrypted password will it work
"SELECT * FROM user_md5 where user_name = 'test' AND password=MD5('test');"
+-----------+----------------------------------+
| user_name | password |
+-----------+----------------------------------+
| test | 098f6bcd4621d373cade4e832627b4f6 |
+-----------+----------------------------------+
1 row in set (0.00 sec)
-
This example we will create a SHA1 encrypted password
- We need to create a table called user_sha1 ((Secure Hash Algorithm). The value is returned as a binary string of 40 hex digits)
"CREATE TABLE user_sha1 (user_name VARCHAR(16), password VARCHAR(40));"
Query OK, 0 rows affected (0.10 sec)
- Now we need to insert a user into the table
"INSERT INTO user_sha1 VALUES ('test',SHA1('test') );"
Query OK, 1 row affected (0.00 sec)
- Now lets see if we query the user with the unencrypted password will it work
"SELECT * FROM user_sha1 where user_name = 'test' AND password=SHA1('test');"
+-----------+------------------------------------------+
| user_name | password |
+-----------+------------------------------------------+
| test | a94a8fe5ccb19ba61c4c0873d391e987982fbbd3 |
+-----------+------------------------------------------+
1 row in set (0.00 sec)
-
This example we will create a AES encrypted password
- We need to create a table called user_aes, ((Advanced Encryption Standard) encryption, you will need to specify the password field to be of type BLOB)
"CREATE TABLE user_aes (user_name VARCHAR(16), password BLOB);"
Query OK, 0 rows affected (0.10 sec)
- Now we need to insert a user into the table
"INSERT INTO user_aes VALUES('test', AES_ENCRYPT('password', 'key') );"
Query OK, 1 row affected (0.00 sec)
- Now lets see the results
"SELECT * FROM user_aes WHERE user_name='test' AND password=AES_ENCRYPT('password','key');"
+-----------+------------------+
| user_name | password |
+-----------+------------------+
| test | ��}��� ���n�
<-� |
+-----------+------------------+
1 row in set (0.00 sec)
- AES provides reversible encryption (provided you have the key), we can obtain the password in plain text format./
- Now lets see the unecrypted password
"SELECT AES_DECRYPT(password,'key') FROM user_aes;"
+-----------------------------+
| AES_DECRYPT(password,'key') |
+-----------------------------+
| password |
+-----------------------------+
1 row in set (0.00 sec)
For further info on encryption in MySQL
|
|
Last Updated on Thursday, 08 May 2008 07:01 |
|
|
Database HowTo's -
MySQL HowTo's
|
|
Written by Allen Sanabria
|
|
Sunday, 10 February 2008 00:11 |
|
Mysql user howto, All these commands were tested on MySQL5
- How to add a user.
- I am using the user test as a example as well as the host localhost as an example...
"CREATE USER 'test'@'localhost' IDENTIFIED BY 'passwd';"
- How to change a users password
- "SET PASSWORD FOR 'test'@'localhost' = PASSWORD('passwd');"
- How to delete a user
- "DROP USER 'test'@'localhost';"
- How to rename a users account
- "RENAME USER 'test'@'localhost' TO 'test1'@'localhost';"
- How to grant different permissions to users
- This statement give the SELECT(query) access to the user test on all databases on localhost...
"GRANT SELECT on *.* TO 'test'@'localhost' IDENTIFIED BY 'passwd';"
- This statement gives the test user super user privileges on all databases on localhost to user test....
"GRANT ALL PRIVILEGES on *.* TO 'test'@'localhost' IDENTIFIED BY 'passwd';"
|
|
Last Updated on Wednesday, 14 May 2008 11:49 |
|
Database HowTo's -
MySQL HowTo's
|
|
Written by Allen Sanabria
|
|
Sunday, 10 February 2008 00:02 |
|
How to create a database?
- By default mysql installs as the root user with no passwd (unless noted otherwise)
- Verify that mysql is running by one way (out of many ways). This is the command....
"ps -fe |grep mysqld |grep -v grep"The output should be similiar to this... /(usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --socket=/var/lib/mysql/mysql.sock)
- Once you verified that mysql is running, log in to mysql using this command
- Run this command..
Now for the all in one Feature......
[root@s15261720 wiki]# ps -e |grep mysqld
7209 ? 00:00:00 mysqld_safe
7242 ? 00:00:12 mysqld
# mysql
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 24415 to server version: 4.1.20
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql>
|
|
Last Updated on Wednesday, 14 May 2008 11:53 |
|
Database HowTo's -
MySQL HowTo's
|
|
Written by Allen Sanabria
|
|
Saturday, 09 February 2008 23:58 |
|
Now that you know how to backup your database,I will show you how to restore it on another database.
-
The most common and basic way to restore, is a restore of a database.
- mysql -u test1 --password=pass1 world < world.sql
-
How about restoring your database to another database over the network with out logging into that box...
- mysqldump -u test1 --password=pass1 world | mysql --host=170.20.10.32 -C world
|
|
Last Updated on Wednesday, 14 May 2008 11:55 |
|
Database HowTo's -
MySQL HowTo's
|
|
Written by Allen Sanabria
|
|
Saturday, 09 February 2008 13:29 |
|
If you've got a website with a database or a custom database running for your applications, it is imperative that you make regular backups of the database...
Following examples are using mysqldump
-
You can use mysqldump to create a simple backup of your database. Here is an example..mysqldump -u username --password=password databasename > backup.sql
- username - this is your database username
- password - this is the password for your database
- databasename - the name of your database
- backupfile.sql - the file to which the backup should be written.
-
To backup multiple databases, you will have to do the following example.
- mysqldump -u test1 --password=pass1 --databases world universe >world_and_universe.sql
-
Now what if we want to back up all of our databases. To do that follow the next example.
- mysqldump -u test1 --password=pass1 --all-databases >alldb.sql
-
Here we are going to show you another way of backing up a databases and certain tables using the --add-drop-table. This essentially adds a drop table statement before every create statement. The purpose of this is so that it will remove any previous copies of the table before recreating it.
- This example will add a drop statement before every create statement.
mysqldump --add-drop-table -u test1 --password=pass1 world > world.sql
- This statement will add a drop statement before the following tables only.
mysqldump --add-drop-table -u test1 --password=pass1 Customers world_users world_passwd> world.sql
|
|
Last Updated on Wednesday, 14 May 2008 11:50 |
|
|
|
|
|
Mar.29
