Your cart is currently empty!
Category: mysql
-
Khóa học miễn phí MySQL – Copy Database nhận dự án làm có lương
MySQL – Copy Database
Table of content
In modern times, companies rely on databases to store crucial information like financial transactions, customer profiles, and employee records. It is very important to maintain regular copies of databases there can always be a chance of data loss from power surges and disk crashes. Therefore, regular backups of databases are crucial for effective data management.
Copy Database in MySQL
In MySQL, copying a database involves creating an exact duplicate of an existing database, including its schema and data. This is almost similar to having a backup of a database. It is important to ensure that any changes made to the original database after the copy is made are also reflected in the copied database, if necessary.
To create a copy of a database SQL Server provides the Copy Database statement. But, this is not available in MySQL. Therefore, to create copy of a database we need to dump the contents of one database to other manually.
The following are three steps that involve in copying a database −
-
First of all, we need to create a new database.
-
Then, we need to export the original database using mysqldump.
-
Finally, importing the exported data into the new database.
Example
First of all, let us create a database in the MySQL server using the following query −
CREATE DATABASE testdb;
We can verify whether the database testdb is created or not using the SHOW DATABASES statement.
SHOW DATABASES;
As we can see the output below, the testdb database has been created successfully.
Database information_schema mysql performance_schema testdb Once the database is created successfully, we need to change the current database to ”testdb”, using the USE statement so that any operations we perform such as creating a table will be stored in this database.
USE testdb;
Now, let us create a table named CUSTOMERS using the CREATE query as follows −
CREATE TABLE CUSTOMERS ( ID INT AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
The following query inserts 7 records into the above-created table −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ), (2, ''Khilan'', 25, ''Delhi'', 1500.00 ), (3, ''Kaushik'', 23, ''Kota'', 2000.00 ), (4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ), (5, ''Hardik'', 27, ''Bhopal'', 8500.00 ), (6, ''Komal'', 22, ''Hyderabad'', 4500.00 ), (7, ''Muffy'', 24, ''Indore'', 10000.00 );
Using the following query, we can verify whether the table CUSTOMERS is created in ”testdb” database or not −
SHOW TABLES;
The table is successfully created in the testdb database.
Tables_in_testdb customers Creating Copy database (Manually)
As discussed earlier in MySQL to create a copy of an existing database we need to create a separate database and dump the contents of it to the newly created database manually.
Following statement creates a database named testdb_copy (to which we need to copy the contents of the testdb database created above).
CREATE DATABASE testdb_copy;
Once both our source (testdb) and destination (testdb_copy) databases are ready, we need to follow the steps given below to manually copy the data from one to another.
Step1 − Open the command prompt, browse through the bin folder of the MySQL server. For instance, we have installed MySQL in the C Program Files directory so, the following command will take us to the bin folder −
C:> CD C:Program FilesMySQLMySQL Server 8.0bin
Step 2 − Using the mysqldump tool, we can copy the database objects and data into a .sql file. Here, we are exporting the contents of the testdb database to a file named “testdb.sql” located at “D:Database_backup”.
Note − The (>) operator is used for exporting the database from one location to another.
mysqldump -u root -p testdb > D:database_backuptestdb.sql
Step 3 − Import the contents of the “testdb.sql” file into the destination database (in our case “testdb_copy”).
Note − The (<) operator is used for importing the database from one location to another.
mysql -u root -p testdb_copy
Verification
To verify whether the data and database object is imported into the testdb_copy database, first, we need to use the current database using the following query in the MySQL Command Line Client −
USE testdb_copy;
If the contents of the testdb are copied in to the testdb_copy successfully, we should be able to find the customers table in the list of tables (which is created earlier).
Therefore, let us verify whether the data from the "testdb" database have been copied to the "testdb_copy" database or not using the following query −
SHOW TABLES;
As we can see in the list below, all the database objects and data have been successfully copied.
Tables_in_testdb customers Copy Database Without MySQLdump
If we want to copy a database without using the mysqldump tool, we must manually create each table in the destination database and copy all the data from the tables present in the current database. This is a repitetive process that should be done for each table that needs to be copied.
Example
Let us create a new database in the MySQL server using the following query −
CREATE DATABASE Tutorials;
We can verify whether the database Tutorials is created or not using the following query −
SHOW DATABASES;
As we can see the output below, the ”Tutorials” database has been created successfully.
Database information_schema mysql performance_schema tutorials Now, we are switching the current database to Tutorials, so that any operations we perform such as creating a table will be stored in this database.
USE Tutorials;
Once we have switched, create a table named CUSTOMERS using the following query −
CREATE TABLE CUSTOMERS ( ID INT AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
Here, we are inserting some records into the table using the INSERT INTO statement below −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ), (2, ''Khilan'', 25, ''Delhi'', 1500.00 ), (3, ''Kaushik'', 23, ''Kota'', 2000.00 ), (4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ), (5, ''Hardik'', 27, ''Bhopal'', 8500.00 ), (6, ''Komal'', 22, ''Hyderabad'', 4500.00 ), (7, ''Muffy'', 24, ''Indore'', 10000.00 );
Using the below query, let us create another database Tutorials_copy, where we want to copy all the data objects and data of the Tutorials database.
CREATE DATABASE Tutorials_copy;
We can verify whether the database Tutorials_copy is created or not, using the following query −
SHOW DATABASES;
The database has been created.
Database information_schema mysql performance_schema tutorials tutorials_copy Now, switch the current database to ”tutorials_copy” using the following query −
USE Tutorials_copy;
Here, we are creating an empty table named “CUSTOMERS” in the ”Tutorials_copy” database with the same schema as the original ”CUSTOMERS” table in the ”Tutorials” −
CREATE TABLE Tutorials_copy.customers LIKE Tutorials.customers;
This query inserts all the data from the original ”customers” table in the ”Tutorials” database into the new ”customers” table in the ”tutorials_copy” database.
INSERT Tutorials_copy.customers SELECT * FROM Tutorials.customers;
We can verify whether the database objects and data from the ”Tutorials” database have been copied to the ”Tutorials_copy” database or not.
SHOW TABLES;
Output
As we can see in the list below, the table has been successfully copied −
Tables_in_tutorials_copy customers Let us also retrieve the records of CUSTOMERS table in ”Tutorials_copy” database to verify whether the records have been copied or not −
Select * from CUSTOMERS;
As we can see the CUSTOMERS table in ”Tutorials_copy” database below, the records have been successfully copied −
ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc
-
Khóa học miễn phí MySQL – Database Info nhận dự án làm có lương
MySQL – Database Info
MySQL usually stores two types of data in it: actual data stored in the form of tables and views, and information about the structure of a database and its objects. Such information is known as Metadata.
For instance, whenever a user forgets certain information of a database or its objects, MySQL provides specific commands to retrieve the said information. There are actually three types of information, which you can retrieve from a MySQL database. They are as follows −
-
Information about the result of queries − This includes the number of records affected by any SELECT, UPDATE or DELETE statement.
-
Information about the tables and databases − This includes information related to the structure of the tables and the databases.
-
Information about the MySQL server − This includes the status of the database server, version number, etc.
It is very easy to get all this information at the MySQL prompt, but while using PERL or PHP APIs, we need to call various APIs explicitly to obtain all this information.
Obtaining Database Info from MySQL Prompt
While accessing a MySQL server from MySQL prompt, which is a Command Prompt in Windows and a Terminal in Linux etc., any information regarding a database using following commands.
-
SHOW DATABASES: This command is used to retrieve the list of all databases present in MySQL.
-
SHOW TABLES: This command is used to display the list of tables present in a database.
-
mysql -V: This command is used to provide the current version of MySQL installed in your system.
-
DESC or DESCRIBE: This command is used to retrieve the structure or definition of a database table.
mysql -V Command
If you want to check the version of MySQL Server installed in your system, use the following mysql -V on Command Prompt or Terminal.
Note: You must remember that the command prompt must be run as an administrator in Windows.
Example
In here, we are checking the current version of MySQL server in command prompt −
C:WindowsSystem32> mysql -V
Output
As we can see the output below, the current MySQL server version is ”8.0.33” −
mysql Ver 8.0.33 for Win64 on x86_64 (MySQL Community Server - GPL)
SHOW DATABASES Command
To list or retrieve the names of all the databases in MySQL, you can use the following SHOW DATABASES command after logging into MySQL server −
Note − This command will list both system databases and user-defined databases together. The user must identify their specific user-defined database all the data is stored in.
Example
In the following query, we are fetching list of databases that exists in the current MySQL server −
SHOW DATABASES;
Here, the first three rows are system databases and the last two rows are user-defined databases −
| Database |
|---|
| information_schema |
| mysql |
| performance_schema |
| tutorials |
| tutorials_copy |
SHOW TABLES Command
To list all the tables in a MySQL database, you can use the SHOW TABLES command after selecting a database with USE command −
Example
In the below query, we are selecting a database named ”Tutorials” −
USE Tutorials;
Now, let us use the SHOW TABLES to fetch all the names of tables present in the above database −
Show Tables;
Output
Following is the list of tables exist in the ”Tutorials” database −
| Tables_in_tutorials |
|---|
| customers |
| employees |
| students |
DESC Command
If we want to check the structure of a MySQL table, we need to use the DESC or DESCRIBE query. DESC is a shortcut for DESCRIBE query, but retrieves the same result.
Example
Here, we are fetching the structure of MySQL CUSTOMERS table −
DESC CUSTOMERS;
Following is the structure −
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| ID | int | NO | PRI | NULL | auto_increment |
| NAME | varchar(20) | NO | NULL | ||
| AGE | int | NO | NULL | ||
| ADDRESS | char(25) | YES | NULL | ||
| SALARY | decimal(18,2) | YES | NULL |
Obtaining the Number of Rows Affected by a Query
Let is now see how to obtain this information.
PERL Example
In DBI scripts, the affected row count is returned by the do( ) or by the execute( ) command, depending on how you execute the query.
# Method 1 # execute $query using do( ) my $count = $dbh->do ($query); # report 0 rows if an error occurred printf "%d rows were affectedn", (defined ($count) ? $count : 0); # Method 2 # execute query using prepare( ) plus execute( ) my $sth = $dbh->prepare ($query); my $count = $sth->execute ( ); printf "%d rows were affectedn", (defined ($count) ? $count : 0);
PHP Example
In PHP, invoke the mysql_affected_rows( ) function to find out how many rows a query changed.
$result_id = mysql_query ($query, $conn_id);
# report 0 rows if the query failed
$count = ($result_id ? mysql_affected_rows ($conn_id) : 0);
print ("$count rows were affectedn");
Listing Tables and Databases
It is very easy to list down all the databases and the tables available with a database server. Your result may be null if you don”t have the sufficient privileges.
Apart from the method which is shown in the following code block, you can use SHOW TABLES or SHOW DATABASES queries to get the list of tables or databases either in PHP or in PERL.
PERL Example
# Get all the tables available in current database.
my @tables = $dbh->tables ( );
foreach $table (@tables ){
print "Table Name $tablen";
}
PHP Example
Try the following example to get database info −
Copy and paste the following example as mysql_example.php −
<html>
<head>
<title>Getting MySQL Database Info</title>
</head>
<body>
<?php
$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''root@123
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
$tutorial_count = null;
if($mysqli->connect_errno ) {
printf("Connect failed: %s<br />", $mysqli->connect_error);
exit();
}
printf(''Connected successfully.<br />'');
if ($result = mysqli_query($mysqli, "SELECT DATABASE()")) {
$row = mysqli_fetch_row($result);
printf("Default database is %s<br />", $row[0]);
mysqli_free_result($result);
}
$mysqli->close();
?>
</body>
</html>
Output
Access the mysql_example.php deployed on apache web server and verify the output.
Connected successfully. Default database is tutorials
Getting Server Metadata
There are a few important commands in MySQL which can be executed either at the MySQL prompt or by using any script like PHP to get various important information about the database server.
| Sr.No. | Command & Description |
|---|---|
| 1 |
SELECT VERSION( ) Server version string |
| 2 |
SELECT DATABASE( ) Current database name (empty if none) |
| 3 |
SELECT USER( ) Current username |
| 4 |
SHOW STATUS Server status indicators |
| 5 |
SHOW VARIABLES Server configuration variables |
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc
icon to run the following MySQL code to be executed on the CUSTOMERS table and retrieve the records matching with the given condition.