Running mysql query from shell script

This is the simple method to run a mysql query from Bash script

Query3=”select * from ta_lookup where gds = ‘wsp’ and server like  ‘reported%’;”
echo   “$Query3” | mysql –host=tiber12 mvc 

 ###### mvc is name of database and –host is the database host

Advertisements

Adding user to Mysql

mysql> GRANT ALL ON cacti.* TO cactiuser@localhost IDENTIFIED BY ‘somepassword’;
mysql> flush privileges;

or adding a new user to MySQL you just need to add a new entry to user table in database mysql. Below is an example of adding new user phpcake with SELECT, INSERT and UPDATE privileges with the password  mypass  the SQL query is :
mysql> use mysql;
Database changed

mysql> INSERT INTO user (host, user, password, select_priv, insert_priv, update_priv) VALUES (‘localhost’, ‘phpcake’, PASSWORD(‘mypass’), ‘Y’, ‘Y’, ‘Y’);
Query OK, 1 row affected (0.20 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT host, user, password FROM user WHERE user = ‘phpcake’;
+———–+———+——————+
| host      | user    | password         |
+———–+———+——————+
| localhost | phpcake | 6f8c114b58f2ce9e |
+———–+———+——————+
1 row in set (0.00 sec)

When adding a new user remember to encrypt the new password using PASSWORD() function provided by MySQL. As you can see in the above example the password mypass is encrypted to 6f8c114b58f2ce9e.

Notice the the FLUSH PRIVILEGES statement. This tells the server to reload the grant tables. If you don’t use it then you won’t be able to connect to mysql using the new user account (at least until the server is reloaded).

You can also specify other privileges to a new user by setting the values of these columns in user table to ‘Y’ when executing the INSERT query :

    * Select_priv
    * Insert_priv
    * Update_priv
    * Delete_priv
    * Create_priv
    * Drop_priv
    * Reload_priv
    * Shutdown_priv
    * Process_priv
    * File_priv
    * Grant_priv
    * References_priv
    * Index_priv
    * Alter_priv

I think you can guess what those privileges serve by reading it’s name

I was just working on a new Bugzilla installation, and noticed their grant command example, and thought I’d share it here as another example:

mysql> GRANT SELECT, INSERT,
       UPDATE, DELETE, INDEX, ALTER, CREATE, LOCK TABLES,
       CREATE TEMPORARY TABLES, DROP, REFERENCES ON bugs.*
       TO bugs@localhost IDENTIFIED BY ‘$db_pass’;

mysql> FLUSH PRIVILEGES;

They also show this syntax for changing the MySQL root user password, which is a little different than the syntax I normally use, so I thought I’d share that here as well:

mysql> UPDATE user
       SET password = password(‘new_password’)
       WHERE user = ‘root’;

mysql> FLUSH PRIVILEGES;

Recover/Reset MySQL root Password

Step # 1 : Stop mysql service

# /etc/init.d/mysql stop
Output:

Stopping MySQL database server: mysqld.

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

# mysqld_safe –skip-grant-tables &
Output:

[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
Output:

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.

mysql>

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

Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
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

What is Usenet News ?

Usenet News is a global electronic bulletin board system in which millions of computer users exchange information on a vast range of topics. The major difference between Usenet News and e-mail discussion groups is the fact that Usenet messages are stored on central computers, and users must connect to these computers to read or download the messages posted to these groups. This is distinct from e-mail distribution, in which messages arrive in the electronic mailboxes of each list member. Usenet itself is a set of machines that exchanges messages, or articles, from Usenet discussion forums, called newsgroups

What are VLANs ?

What are VLANs ?

VLANs are broadcast domains in a Layer 2 network. Each broadcast domain is like a distinct virutal bridge within the switch. Each virtual bridge you create in a switch defines a broadcast domain. By default, traffic from one VLAN cannot pass to another VLAN. Each of the users in a VLAN would also be in the same IP Subnet. Each switch port can belong to only one VLAN.