Are you a DBA in one particular discipline say Oracle, SQL Server, MySQL. Way back 10 years if you know any one of these you are good to go. Your job is guaranteed for another few years. Now the IT demographics has been shifting continuously and its time to rethink about the future if career. The way data is handled has drifted from conventional relational model to latest unstructured model. To handle such humongous volume of data which grows big day by day it becomes mandate to start learning the NoSQL databases that handle big data. MongoDB is a popular NoSQL database brand that is gaining popularity at lightning pace.MySQL relational database is growing at rapid pace in addition to oracle for supporting websites.
MySQL the most popular open source database was acquired by Sun microsystems. Later Oracle corporation acquired Sun. Now Mysql is popular among website companies owing to its flexibility. Owing to its scare availability of mysql DBA's it is a hot skill. Moe than 1200+ practical mysql dba interview questions will be updated soon.
What does mysql dba do?
MySQL is gaining popularity among the web developers community owing to its ease of usage and low cost.We are going to discuss more posts on various tasks performed by MySQL DBA. We'll be updating details on the following MySQL DBA responsibility details, information on MySQL database and many more :
1) Connecting And Disconnecting From MySQL
2) Issuing MySQL Commands
3) Creating and using database in MySQL
4) Create user in MySQL
5) Drop user in MySQL
6) Using MySQL utilities like mysqladmin, mysqlimport and many more
1) How do I access mysql database?
We can invoke mysql comman-line interface by simply typing mysql at the command prompt. The command is the same for windows and Linux
2) How can you get details on databases?
The command show database; helps us get information on the mysql databases
MySQL on Linux Database Common tasks:-
Log Into MySQL Server Ubuntu Linux :
We've seen steps on installing mysql server in ubuntu linux, checking if mysql service is up and running. We can log into mysql server using the root user and password.
root@ubuntu:~# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 39
Server version: 5.1.41-3ubuntu12.6 (Ubuntu)
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>
MySQL Database Create Use Ubuntu Linux :
First step in using a mysql database is creating a new database. We need to use it next. In this database we can create tables, index,procedures and such database objects.This is in mysql ubuntu linux.
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
Mysql pi() Function Ubuntu Linux :
In ubuntu linux we can make use of scientific functions. These are also called mathematical functions. Here is how we use the function
mysql> select pi();
+----------+
| pi() |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)
mysql> select pi() * 2;
+----------+
| pi() * 2 |
+----------+
| 6.283185 |
+----------+
1 row in set (0.00 sec)
Mysqlcheck Command Test Table:
We can test the tables in a mysql database using the mysqlcheck utility. As we've set the password we need to supply the password using -p option while using this command from shell. Mysqlcheck is used to check, repair the tables in a database.Here is how it works
root@ubuntu:~# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 48
Server version: 5.1.41-3ubuntu12.6 (Ubuntu)
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table test(id integer);
Query OK, 0 rows affected (0.07 sec)
root@ubuntu:~# mysqlcheck -p test test
Enter password:
test.test OK
Unified Oracle SSO Credentials For MySQL.com Authentication :
Oracle corporation the DB giant has acquired sun microsystems few years back as a part of which MySQL has now become an integral part of Oracle product suite.
Ever since acquisition Oracle has been revamping lot on Sun Solaris, MySQL
As a new move from ORacle corporation it is now possible to use the oracle.com account to log onto mysql.com. Oracle SSO credentials are validated with mysql.com authentication. This helps you have one single account to access multiple portals
This is a marketing strategy from Oracle corporation that projects power of its Oracle SSO suite of products to be used with its own websites.
If you dont have an accoutnwith oracle.com create one and use it with mysql.com
1) How to check Mysql Service in ubuntu Linux?
We can check if the mysql service is up and running in the ubuntu linux using the service command. It is easy and simple as follows:
root@ubuntu:~# service mysql status
mysql start/running, process 32478
2) How to install MySQL in Ubuntu Linux Server?
It is very easy to install mysql in ubuntu linux server.We can simply install the mysql package in the ubuntu linux server very easily. All we need is a apt-get install ubuntu linux command. Here is how it goes about.
root@ubuntu:~# apt-get install mysql-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
libdbd-mysql-perl libdbi-perl libhtml-template-perl libnet-daemon-perl
libplrpc-perl mysql-client-5.1 mysql-client-core-5.1 mysql-server-5.1
mysql-server-core-5.1
Suggested packages:
dbishell libipc-sharedcache-perl tinyca mailx
The following NEW packages will be installed:
libdbd-mysql-perl libdbi-perl libhtml-template-perl libnet-daemon-perl
libplrpc-perl mysql-client-5.1 mysql-client-core-5.1 mysql-server
mysql-server-5.1 mysql-server-core-5.1
0 upgraded, 10 newly installed, 0 to remove and 41 not upgraded.
Need to get 21.2MB of archives.
After this operation, 50.4MB of additional disk space will be used.
Do you want to continue [Y/n]? Y
Get:1 http://us.archive.ubuntu.com/ubuntu/ lucid/main libnet-daemon-perl 0.43-1 [46.9kB]
Get:2 http://us.archive.ubuntu.com/ubuntu/ lucid/main libplrpc-perl 0.2020-2 [36.0kB]
Get:3 http://us.archive.ubuntu.com/ubuntu/ lucid/main libdbi-perl 1.609-1build1 [798kB]
Get:4 http://us.archive.ubuntu.com/ubuntu/ lucid/main libdbd-mysql-perl 4.012-1ubuntu1 [135kB]
Get:5 http://us.archive.ubuntu.com/ubuntu/ lucid-updates/main mysql-client-core-5.1 5.1.41-3ubuntu12.6 [177kB]
Get:6 http://us.archive.ubuntu.com/ubuntu/ lucid-updates/main mysql-client-5.1 5.1.41-3ubuntu12.6 [8,138kB]
Get:7 http://us.archive.ubuntu.com/ubuntu/ lucid-updates/main mysql-server-core-5.1 5.1.41-3ubuntu12.6 [4,712kB]
Get:8 http://us.archive.ubuntu.com/ubuntu/ lucid-updates/main mysql-server-5.1 5.1.41-3ubuntu12.6 [7,008kB]
Get:9 http://us.archive.ubuntu.com/ubuntu/ lucid/main libhtml-template-perl 2.9-1 [65.8kB]
Get:10 http://us.archive.ubuntu.com/ubuntu/ lucid-updates/main mysql-server 5.1.41-3ubuntu12.6 [93.6kB]
Fetched 21.2MB in 35s (606kB/s)
Preconfiguring packages ...
Selecting previously deselected package libnet-daemon-perl.
(Reading database ... 125251 files and directories currently installed.)
Unpacking libnet-daemon-perl (from .../libnet-daemon-perl_0.43-1_all.deb) ...
Selecting previously deselected package libplrpc-perl.
Unpacking libplrpc-perl (from .../libplrpc-perl_0.2020-2_all.deb) ...
Selecting previously deselected package libdbi-perl.
Unpacking libdbi-perl (from .../libdbi-perl_1.609-1build1_i386.deb) ...
Selecting previously deselected package libdbd-mysql-perl.
Unpacking libdbd-mysql-perl (from .../libdbd-mysql-perl_4.012-1ubuntu1_i386.deb) ...
Selecting previously deselected package mysql-client-core-5.1.
Unpacking mysql-client-core-5.1 (from .../mysql-client-core-5.1_5.1.41-3ubuntu12.6_i386.deb) ...
Selecting previously deselected package mysql-client-5.1.
Unpacking mysql-client-5.1 (from .../mysql-client-5.1_5.1.41-3ubuntu12.6_i386.deb) ...
Selecting previously deselected package mysql-server-core-5.1.
Unpacking mysql-server-core-5.1 (from .../mysql-server-core-5.1_5.1.41-3ubuntu12.6_i386.deb) ...
Selecting previously deselected package mysql-server-5.1.
Unpacking mysql-server-5.1 (from .../mysql-server-5.1_5.1.41-3ubuntu12.6_i386.deb) ...
Selecting previously deselected package libhtml-template-perl.
Unpacking libhtml-template-perl (from .../libhtml-template-perl_2.9-1_all.deb) ...
Selecting previously deselected package mysql-server.
Unpacking mysql-server (from .../mysql-server_5.1.41-3ubuntu12.6_all.deb) ...
Processing triggers for man-db ...
Processing triggers for ureadahead ...
Setting up libnet-daemon-perl (0.43-1) ...
Setting up libplrpc-perl (0.2020-2) ...
Setting up libdbi-perl (1.609-1build1) ...
Setting up libdbd-mysql-perl (4.012-1ubuntu1) ...
Setting up mysql-client-core-5.1 (5.1.41-3ubuntu12.6) ...
Setting up mysql-client-5.1 (5.1.41-3ubuntu12.6) ...
Setting up mysql-server-core-5.1 (5.1.41-3ubuntu12.6) ...
Setting up mysql-server-5.1 (5.1.41-3ubuntu12.6) ...
mysql start/running, process 32478
Setting up libhtml-template-perl (2.9-1) ...
Setting up mysql-server (5.1.41-3ubuntu12.6) ...
Processing triggers for libc-bin ...
ldconfig deferred processing now taking place
3) Describe some information on dbmail Mysql dependency Package onto ubuntu Linux?
I tried installing mysql database in ubuntu linux and found that some dependency packages have to be installed before installing mysql. dbmail is an interesting package that can be installed in ubuntu linux.
root@ubuntu:~# apt-get install dbmail
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
libgmime-2.0-2a libsieve2-1
Suggested packages:
dbmail-pgsql dbmail-mysql sqlite3
The following NEW packages will be installed:
dbmail libgmime-2.0-2a libsieve2-1
0 upgraded, 3 newly installed, 0 to remove and 41 not upgraded.
Need to get 594kB of archives.
After this operation, 1,786kB of additional disk space will be used.
Do you want to continue [Y/n]? Y
Get:1 http://us.archive.ubuntu.com/ubuntu/ lucid/universe libgmime-2.0-2a 2.2.22-5 [200kB]
Get:2 http://us.archive.ubuntu.com/ubuntu/ lucid/universe libsieve2-1 2.2.6-1 [76.2kB]
Get:3 http://us.archive.ubuntu.com/ubuntu/ lucid/universe dbmail 2.2.11-1build1 [317kB]
Fetched 594kB in 1s (374kB/s)
Preconfiguring packages ...
Selecting previously deselected package libgmime-2.0-2a.
(Reading database ... 125127 files and directories currently installed.)
Unpacking libgmime-2.0-2a (from .../libgmime-2.0-2a_2.2.22-5_i386.deb) ...
Selecting previously deselected package libsieve2-1.
Unpacking libsieve2-1 (from .../libsieve2-1_2.2.6-1_i386.deb) ...
Selecting previously deselected package dbmail.
Unpacking dbmail (from .../dbmail_2.2.11-1build1_i386.deb) ...
Processing triggers for man-db ...
Processing triggers for ureadahead ...
Setting up libgmime-2.0-2a (2.2.22-5) ...
Setting up libsieve2-1 (2.2.6-1) ...
Setting up dbmail (2.2.11-1build1) ...
Creating config file /etc/dbmail/dbmail.conf with new version
Creating config file /etc/default/dbmail with new version
* Starting dbmail servers [ OK ]
Processing triggers for libc-bin ...
ldconfig deferred processing now taking place
1) How do you find version of mysql?
We can make use of one of the following commands :
mysql –version
mysqladmin –version
mysql>select version();
2) Can we use mysqladmin to start the mysql server?
No. We can’t use mysqladmin to start the mysqld.
3) How do you stop the mysqld(mysql server) using mysqladmin?
mysqladmin -uroot -ppassword shutdown
4) What is the default administrative account in mysql grant system?
The default administrative account is “root”.
5) How do you determine the status of variables?
mysql>show status;
$mysqldmin extended-status
6) How do you obtain information on client processes?
We can obtain list of client processes connected to the server with the following command:
mysql>show processlist;
7) Can a regular user be able to see all process threads?
No.Only users with “PROCESS” privilege can see a complete list. REgular users can see their own threads.
8) What can users with “SUPER” privilege do?
Users with “SUPER” privilege are the most powerful. They can even kill running threads with the KILL command.
mysql>kill thread-number(eg:1,2…);
MySQL TABLE MOVE RENAME DATABASE :
This is a sample test that I performed in my mysql lab today. I created two different databases. I created a new table in one database. I moved it to another database with its name renamed. I tried moving the old table to seconds database with the original name and this gave an error
mysql> create database first;
Query OK, 1 row affected (0.01 sec)
mysql> create database second;
Query OK, 1 row affected (0.01 sec)
mysql> create table first.old (id int);
Query OK, 0 rows affected (0.10 sec)
mysql> alter table first.old rename second.new;
Query OK, 0 rows affected (0.03 sec)
mysql> alter table first.old rename second.old;
ERROR 1146 (42S02): Table ‘first.old’ doesn’t exist
Here are my findings regarding MySQL tables
1) Tables can be moved across two different databases
2) Tables can be renamed while moving from one database to another
3) Original table becomes unavailable after performing MOVE operation
Career Prospects MySQL DBA :-
Mysql the light weight most sought after database used prominently in web application development that is made use of by giants including facebook.com is gaining popularity day by day.
There are plenty of options in Mysql discipline. why am I so particular about mysql DBA?
There is certainly a reason that made me think a lot into this and come to a conclusion on mysql DBA profile.
As companies start migrating towards cloud slowly, the profile that is in danger is the DBA as applications hosted in cloud dont need a DBA to optimize their performance.
When it comes to cloud nosql databases including mongodb is made use of extensively. Considering the resemblance of mongodb with mysql the change of career from mysql dba to mongoDB will be an easy approach to safeguard your career
What is company operates in a non-cloud environment?
Still this light weight easy to maintain that is easily inter-operable with open source programming languages including perl,php,python,ruby on rails that have good drivers developed to support mysql make it the choice of many startups including popular facebook,twitter,tumblr to name a few. Start learning mysql today
Give details on mysql backup types:-
MySQL database the choice of many web techs these days is light weight and offers interesting backup options. Mysql backups can be broadly classified into two major categories:
1) Physical backups - This is copy of files and directories at file system level. This involves backup of datafiles, configuration files of mysql
2) Logical backups - This is an interesting backup that lets you perform backup at logical structure level, create dumps that can be imported against. This doesnt involve backup of physical files and directories
How is MySQL tee Command to spool output to logfile?
In Oracle we make use of spool filename command to spool the output of a sql statement onto an output file. similar to that in mysql we have a command called tee that logs the output of a sql command to output file
tee filename.txt
show variables
The above command will spool the output to a file called filename.txt
In Oracle the above command equivalent will be
spool filename.txt
show parameter parametername
spool off
Why MySQL Is Becoming Hot?
Ever since acquisition of Sun microsystems by Oracle corporation, Mysql the open source database is gaining momentum as opposed to what has benn thought originally. Considering the open source popularity Oracle has rebranded it as Oracle MySQL
These days if you look at job requirement it is a combo skill that employers are looking for. MySQL is definitely a keyword that will be a mandate. some employers are willing to train their employees and are looking for passion to learn MySQL
Being so small, simple, having small footprint, tight integration with web scripts like PHP/Perl, easy maintenance and lots of interesting features has made MySQL a red hot skill that can come as a great secondary skill as DBA and developer
Corporates are in cost cutting mode and this has made them turn their focus on open source technologies and framework that is free. Some of the technologies that offer same interesting feature as their licensed counterparts are in great demand
In programming arena, OOP also called the object oriented programming is the base on which big technological giants including google, facebook are built.
Considering this it is always a good idea to learn a free open source programming language. Java is most popular and is exhaustive. To cater to growing demand companies go behind agile techniques both in project management as well as technology architecture choice. This has led to choice of scripting languages like Ruby on rails, PHP/Mysql stack, python, perl over java, .NET
If you prefer to land in your next dream job at twitter, facebook, google don't wait today. Grab a PHP/MySQL, javascript book from your library and start learning today
How to change column datatype of table in mysql?
Whenever we create a table in a mysql database, we can later change the datatype of the table column as follows
alter table tablename change columnname columnname newdatatype;
Give details on built-in functions in mysql?
There are plenty of functions in-built in mysql database that helps us obtain information on user, hostname, day,time,day of the year. We can connect using mysql client as root user and password. Here is a simple set of inbuilt function demonstration.
mysql> select dayofyear('2010-09-09');
+-------------------------+
| dayofyear('2010-09-09') |
+-------------------------+
| 252 |
+-------------------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2010-09-28 |
+------------+
1 row in set (0.00 sec)
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 11:32:35 |
+----------------+
1 row in set (0.00 sec)
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
Give details on MySQL Tables Database Information Schema:-
In MySQL database whenever we log into it as a root user, we land in the default database information_schema. From there we can list the tables in all other databases within the system. Here are simple steps demonstrating this process
mysql> create database infopedia;
Query OK, 1 row affected (0.01 sec)
mysql> show tables from infopedia;
Empty set (0.00 sec)
mysql> use infopedia;
Database changed
mysql> create table new (id INT);
Query OK, 0 rows affected (0.03 sec)
mysql> use information_schema;
Database changed
mysql> show tables from learnermydbsreference;
+---------------------+
| Tables_in_mydb |
+---------------------+
| new |
+---------------------+
1 row in set (0.00 sec)
MyISAM Table InnoDB MySQL Database:-
If you'd have tried creating tables in a mysql database under the C:mysqldata directory you will see folders corresponding to the database created. Lets say if we create a new database named infopedia using mysql command :we see a folder named mydb created under C:\mysqldata. It will be of form C:\mysqldata\mydb
Inside this folder we see a file named db.opt . This file is of type OPT (I make use of Windows OS) and stores details on the characteristics of database.
Now comes the interesting concept of database engine. Some popular MySQL database engines are - MyISAM, InnoDB, Memory, ARCHIVE,MGR_TARGET. If we make use of GUI phpMyadmin we get this options as a drop down menu while creating table. Each storage engine has its unique benefit. Two most popular storage engines are Myisam and innodb. By default if a table is created in a database it is assigned engine=myisam. If we want to create a table of type innodb we need to specify the option engine=innodb while creating the database.
mysql> use mydb;
Database changed
mysql> create table test_myisam(id int) engine=myisam;
Query OK, 0 rows affected (0.02 sec)
The above query will create a table of type myisam. There will be three files test_myisam.frm, test_myisam.MYD, test_myisam.MYI created under the folder infopedia directory (C:mysqldatainfopedia location)
mysql> create table test_innodb(id int) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
This query creates a table of type innoDB. We see file of form test_innoDB.frm under the mydb directory (C:\mysqldata\mydb location)
phpmyadmin mysql date error:-
I make use of phpmyadmin interface to create a table with column todaysdate of datatype date. I tried setting default value using current_date() and now() functions. I got the error.Later I found that date values are assigned by default.
phpMyAdmin MySQL GUI Engine Types:-
phpMyAdmin is a GUI tool that makes creation and management of mysql database objects very easy and user friendly. This is a graphical demonstration of storage engine types. By default MyISAM tables are created. innoDB is another interesting storage engine type.
MySQL Prepared Statements:-
MySQL Prepared Statements are used when we want to execute mny SQL statements tht differ slightly.They are comparable to substitution variables in ORacle where the value is passed on during run-time.They are very efficient as parsing of query is done once and values are substituted dynamically.
A good example on prepared statement is given below :
1) mysql>prepare preparestatement_name from 'query';
Say,
mysql>prepare my_example from 'select count(*) from emp where dept = ?';
2) mysql> set @dept='value'; execute my_example using @dept;
3) mysql> DEALLOCATE PREPARE my_example;
Following are the sequence of steps followed :
1) Create and name prepared statement. The FROM clause is followed by query.
2) Set the dynamic vriable value.
3) Execute the prepared statement.
4) Deallocate the prepared statement.
Mysql my.cnf my.ini my file:-
I tried configuring replication in my MySQL database instance. I had WAMP installed in my laptop and MySQL has come as an integral part of it. For enabling replication the it is essential to enable binary logging, set server-id in the my.cnf (or) my.ini file. I was looking out for this file but couldn't find one. I instead found my file under C:mysql. I made changes to this file (this can be done by shutting down the mysql server instance first). Then I restarted the instance.
MySQL Default Databases List :
In a mysql database there are few default databases created at the time of installation. If we connect as root user and get the list of databases we get the following result
mysql> show databases;
+----------------------------+
| Database |
+----------------------------+
| information_schema |
| mysql |
+----------------------------+
2 rows in set (0.04 sec)
1) How do you find version of mysql?
We can make use of one of the following commands :
mysql --version
mysqladmin --version
mysql>select version();
2) Can we use mysqladmin to start the mysql server?
No. We can't use mysqladmin to start the mysqld.
3) How do you stop the mysqld(mysql server) using mysqladmin?
mysqladmin -uroot -ppassword shutdown
4) What is the default administrative account in mysql grant system?
The default administrative account is "root".
5) How do you determine the status of variables?
mysql>show status;
$mysqldmin extended-status
6) How do you obtain information on client processes?
We can obtain list of client processes connected to the server with the following command:
mysql>show processlist;
7) Can a regular user be able to see all process threads?
No.Only users with "PROCESS" privilege can see a complete list. Regular users can see their own threads.
8) What can users with "SUPER" privilege do?
Users with "SUPER" privilege are the most powerful. They can even kill running threads with the KILL command.
mysql>kill thread-number(eg:1,2...);
9) How to log into mysql database?
Logging into MySQL is done using username and password. In addition to this we need to specify the hostname - name of the machine where the MySQL server runs.
mysql ---- client program used to connect to the mysql server.
mysql -uusername -ppassword -hhostname
The syntax mentioned above is used to connect to a mysl server.
where,
username - name of the user
password - password
hostname - host where server resides
10) How to check is server is up and running using mysqld command?
Mysqladmin is the tool popular among the mysql dba's primarily used for database administration purpose. We can use the ping option to see if mysqld is alive or not.
root@ubuntu:~# mysqladmin -uroot -p ping
Enter password:
mysqld is alive
11) Give details on mysql architecture:-
MySQL Architecture is a client server architecture where server runs on one machine and client connects to the server machine.The server daemon is mysqld which is started in server with many different options.
There are many ways to check if a mysqld is up and running.
In an UNIX machine issue the following command:
ps -efgrep mysqldgrep -v mysqld
In windows navigate :
Control Panel->Administrative Services->Services->MySQLService(name given at the time of creation of mysql service).Check the status of service. If it is start we can start using the system.
12) What are the methods to perform backup in mysql database:-
MySQL Database Backups are made by DBA to safeguard the database against the possibility of system crash or hardware failure that may result in a data loss or corruption.
Backups are used as a protection against human errors like database and table removal by mistake
We set move MySQL installation from one server to another, set up a replication server we need a backup of the database
13) Give details on Shell Script to test and start mysql server:-
This is the shell script to test if the mysql server is up and running. If so ignore it. If not we can start the mysqld daemon.
#!/bin/sh
process=`ps -U mysql|wc -l`
results=`ps -U mysql > new`
res=`sed -n 2p new`
echo Result is $res // checkpoint
echo $process
if [ $process -lt 2 ]; then
echo "Start the server"
service mysqld start
else
echo "Server already up and running"
fi
14) What is the reason behind Warning: mysqli_connect() [function.mysqli-connect]: (42000/1049): Unknown database 'databasename' in 'path' on line x?
Warning: mysqli_connect() [function.mysqli-connect]: (42000/1049): Unknown database 'databasename' in 'path' on line x
Error message specified in die function of mysqli_connect where x=1,2,3...
mysqli_connect ( ) is the PHP function used for establishing connection between PHP script and mysql database....
mysqli_connect("hostname","username","password","databasename") or die ("Error Message" );
Eg :
mysqli_connect("hostname","username","password","databasename") or die ("Not Found" );
This error occurs if a wrong databasename or a database that is non existant is specified in databasename part.
It is a good practice to use die ( ) function to catch the errors and display messages.
15) What is the reason behind ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)?
I had WAMP installed in my laptop. I tried connecting to mysql database by issuing mysql and got the error ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061). I thought that the problem is with root password. On careful examination I found that the WAMP server is down. I again restarted WAMP server and the problem got fixed. The major flexibility with using WAMP is that we can manage each services (Apache,PHP,MySQL) individually though they come together as a single bundle.
C:wampbinmysqlmysql5.1.36bin>mysql -uroot -p
Enter password: ********
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)
C:wampbinmysqlmysql5.1.36bin>mysql -uroot -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.1.36-community-log MySQL Community Server (GPL)
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>
This works fine now.
Mysqladmin command line tool usage:
MySQL Database ,the open source database widely used today because of its cost-effective nature is gaining momentum.Organizations are migrating from different databases to MySQL.
MySQL is on 5.1 release and have come out with 6.0 beta version.MySQL has its own certification programme geared towards developers and DBA's.We will be discussing on MySQL.
The command-line tool used for MySQL server administration is mysqladmin.
Loction : /usr/local/mysql/bin/mysqladmin
Few Uses :
1) Perform mysql administration tasks such as shutdown
2) Reload mysql privileges ( mysql grant tables)
2) user administration ( changing mysql administrator password)
Usage : mysqladmin command
command can be one of the following:
reload - reloads mysql grant tables
status - server state information
ping - test if server is alive
shutdown - shutsdown the mysql server
refresh - resets all caches and logs
variables - returns value of all server variables
processlist - returns list of all processes active on the server
kill - kills an active session process
version - display mysql server version
password - changes a user password
To obtain help on mysqladmin usage issue : mysqladmin --help
GUI lternatives of mysqladmin :
Tasks performed by mysqladmin tool can be performed by certain GUI ( Graphical User Interface) tools
1) pnpMyAdmin
2) Mysql control center
3) Winmysqladmin
How can you get details on warnings CREATE TABLE IF NOT EXISTS Table_name Command?
1) Log into the MySQL Database Server from command-line client mysql/phpMyAdmin :
$mysql -u root -ppassword
mysql>
2) List the databases and choose the database. If it is a fresh install, create a new database.
mysql> show databases; - Lists databases
mysql> use database_name; - Where database_name is the name of database we use. Say firstexample is the name of the database :
mysql> use firstexample;
mysql> show tables; - Lists tables in firstexample
If no database exists, create a new database and use it.
mysql> create database firstexample;
mysql> use firstexample;
mysql> show tables;
3) Create a table with a unique name. Check if the table with samename already exists:
mysql> create table if not exists table_name;
We get Warnings, if table with samename already exists and the table doesn't get created.
4) To get more details on warnings, use :
mysql> show warnings;
Level : Note
Code : 1050
Message : Table 'table_name' already exists
How to change root password of mysql database?
I've recently installed WAMP Server in my laptop. MySQL comes as a part of WAMP (Windows+Apache+MySQL+PHP). I'll be blogging interesting articles on MySQL database administration, development. Here is the first post on changing root password in MySQL database.
Invoke mysql executable and login as root user. It will have no password set initially
C:mysql -uroot -p
mysql> use mysql
mysql> update user set password=PASSWORD("pass") where User="root";
Query OK, 2 rows affected (0.07 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> flush privileges ;
Query OK, 0 rows affected (0.02 sec)
mysql>quit
It is different than Oracle. Here we update value in admin table user.
