MySQL
MySQL
How to add a new user to MySQL and set privileges
Dec 3rd
First you need to connect to MySQL server with a user who has administrative priveleges.
[root@techpulp ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.0.67 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
Let us assume you are creating an user named “neo” with password “neopass“and you would like to grant all privileges on the database “articles“.
mysql> GRANT ALL ON articles.* to 'neo'@'localhost' identified by 'neopass' WITH GRANT OPTION;
After adding a new user, you need More >
How to change/set/reset MySQL root password using mysqladmin
Oct 10th
After installing MySQL service, administrator should set a reasonably strong password using mysqladmin utility. In MySQL, user name of administrator is “root”. To change/set/reset password, you need to run two commands. The following shows the syntax of the commands.
[root@techpulp ~]# mysqladmin -u root password NEWPASSWORD [root@techpulp ~]# mysqladmin -u root -h HOSTNAME password NEWPASSWORD
The following commands show example usage.
[root@techpulp ~]# mysqladmin -u root password y5FxEtrh [root@techpulp ~]# mysqladmin -u root -h localhost password y5FxEtrh
As the commands you executed are stored in history file (~/.bash_history), you can avoid it by running following command before running mysqladmin utility.
[neo@techpulp ~]# echo $HISTFILE /home/neo/.bash_history More >
How to bind my mysql server to a specific IP address
Sep 6th
MySQL server contains a file my.cnf in /etc direcory of Linux. This file contains configuration of MySQL server as well as the configuration parameters of the MySQL client.
To make MySQL server listen on a specific IP address, you need to add a line similar to the following with IP address of your choice under mysqld section.
[root@techpulp ~]# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 # To allow mysqld to connect to a MySQL Cluster management daemon, uncomment # these lines More >
How to resolve “Access denied for user” error with MySQL database connection
Nov 11th
Typically “Access denied for user” error occurs while connecting to MySQL Database because the privileges are not properly set at the server.
Let us examine the following real-time scenario where a user from the host 192.168.1.1 is trying to connect to MySQL server running on 192.168.1.6.
My PC MySQL Server 192.168.1.1 ---------- 192.168.1.6
Let us solve the problem if user encounters an error like the following while connecting to the server.
[neo@techpulp ~]# mysql -u root -p -h 192.168.1.6 Enter password: ERROR 1045 (28000): Access denied for user 'root'@'192.168.1.1' (using password: YES) [neo@techpulp ~]#
To solve this problem, enough privileges should be assigned for the More >
How to rename a table in MySQL
Oct 9th
The RENAME TABLE statement can be used to rename an existing table in MYSQL. The following example shows how name of a table changed from tbl_old to tbl_new.
mysql> RENAME TABLE `tbl_old` TO `tbl_new`; Query OK, 0 rows affected (0.00 sec) mysql>
LIST TABLES statement can be used to view the list of tables present in a database.
How to backup MySQL database
Oct 4th
The MySQL utility command mysqldump can be used to take backup of MySQL database. The output generated from this command can be used to create the same database in another host.
In the following example, we try to login to MySQL server as “root” with password and try to dump the contents of database “mydb“.
[neo@techpulp ~]# mysqldump -u root -p mydb > mydb-backup.sql Enter password: [neo@techpulp ~]# ls -l mydb-backup.sql -rw-rw-r-- 1 neo neo 23876589 2008-10-04 03:29 mydb-backup.sql [neo@techpulp ~]#
The output present in the file mydb-backup.sql will contain a series of MySQL statements and exactly same database can be rebuilt from scratch using More >
Recent Comments