MySQL – Ignore Tables from Backup Question – How do I skip certain tables from MySQL database backup? You can use –ignore-table option with the mysqldump command to ignore table for backup. Using this option, you need to specify both the database and table names as showing in the below example. mysqldump -u root -p DB_NAME –ignore-table=DB_NAME.table1 > database.sql To ignore multiple tables specify the option per table. mysqldump -u root -p DB_NAME –ignore-table=DB_NAME.table1 –ignore-table=DB_NAME.table3 > database.sql
Read MoreTag: mysql
Node.js – MySQL Connection
Use mysql module available on npmjs repository to connect MySQL database and execute queries from Node.js application. Install Node.js MySQL Driver You must have MySQL module installed in your application. You can install it using the following command. npm install mysql Node.js Connect to MySQL Create a JavaScript file in your application and edit it in your favorite text editor. vim connect_mysql.js Add the following content. Change the MySQL connection values as per your system setup.
1 2 3 4 5 6 7 8 9 10 11 12 | var mysql = require('mysql'); var dbconn = mysql.createConnection({ host: "localhost", port: "3306", user: "root", password: "secure_password" }); dbconn.connect(function(err) { if (err) throw err; console.log("Database connected successfully!"); }); |
Test Connection Finally, execute the connect_mysql.js file using node. On successful connection, it…
Read MoreMySQL – Drop Table
Use DROP TABLE statement to delete any existing table in MySQL database. This deletes all table data and structure both. Syntax: DROP TABLEtable_name; Example – MySQL Drop Table For example, I have table users in the database. I need to remove its data completely with structure. Login to your MySQL server and select database. Now execute below query to remove the database. DROP TABLE users;
Read MoreMySQL – Truncate Table
Use MySQL TRUNCATE TABLE statement to delete all table content. This doesn’t remote table structure. The truncate table also reset the auto increment values. Syntax: TRUNCATE TABLE table_name MySQL Truncate Table Statement For example to remove all data from table named users use following command. You must select database first before running below command.
1 | mysql> TRUNCATE TABLE users; |
Example – MySQL Truncate Table For example, I have a table named users with following records in a database. First, check the available data in the table. After that truncate table and again check.
1 2 3 4 5 6 7 8 9 | mysql> SELECT * FROM users; +----+-----------+-----------------+--------------------+---------------------+--------+ | ID | username | password | email | signup_date | status | +----+-----------+-----------------+--------------------+---------------------+--------+ | 1 | admin | $P$BXq.6qP/fAtg | rahul@example.com | 2017-11-04 19:11:53 | 1 | +----+-----------+-----------------+--------------------+---------------------+--------+ | 2 | tecadmin | $P$Kxn3.s8KD/39 | hello@example.com | 2017-11-03 07:37:07 | 0 | +----+-----------+-----------------+--------------------+---------------------+--------+ 2 row in set (0.01 sec) |
…
Read MoreMySQL – Create Table
Use CREATE TABLE statement to create a new table in MySQL database. You must create and select a database to create tables in it. Syntax: CREATE TABLE [IF NOT EXISTS] table_name ( coloumn1, coloumn2 ) engine=table_type; Here [IF NOT EXISTS] is used to create table only if there are no existing table with the name. The database engine can be used as per your requirements. A column can be defined as following. column_name data_type[size] [NOT NULL|NULL] [DEFAULT value] [AUTO_INCREMENT], Example: For example, create table users to store information of our…
Read MoreMySQL – Show Databases
Use SHOW DATABASES statement to list all database on MySQL database server. First login to MySQL server with privileged user and list all databases. Command:- SHOW DATABASES; Example:- Login to your MySQL server using command line. You will get MySQL database prompt like mysql> . Now use SHOW DATABASES statement to list all database. mysql> SHOW DATABASES; +——————–+ | Database | +——————–+ | information_schema | | mysql | | mydb | | performance_schema | +——————–+ 4 rows in set (0.03 sec) You can also use the following command directly on…
Read MoreMySQL – Drop Database
Use DROP statement to delete database using MySQL query prompt. This tutorial will help you to delete MySQL database using the command line. Syntax:- DROP database_name; Example: Login to your MySQL server using command line. You will get MySQL database prompt like mysql> . Now use following command to connect mydb database. mysql> DROP mydb; Another Example: You can also use the following command directly on operating system command prompt without login to MySQL. This help users to automate task inside a script. mysqladmin -u root -p drop mydb
Read MoreMySQL – Select Database
In this chapter of MySQL Tutorial, You will learn how to use database in MySQL. Before executing any operation on database you must need to use that database. Syntax: USE database_name; Example: ogin to your MySQL server using command line. You will get MySQL database prompt like mysql> . Now use following command to connect mydb database. mysql> use mydb;
Read MoreSQL – Create Database
SQL CREATE DATABASE Use CREATE DATABASE statement to create a new database using mysql command. Login to MySQL server with administrative access to create a database. Syntax CREATE DATABASE database_name; Example Login to your MySQL server using the command line. You will get MySQL database prompt like mysql> . Now use CREATE DATABASE statement to create a database. mysql> CREATE DATABASE mydb; You can also use the following command directly on operating system command prompt without login to MySQL first. $ mysqladmin -u root -p create mydb To confirm that…
Read MoreMySQL Tutorial
This is the complete MySQL tutorial for beginner to learn SQL Database online. Using this MySQL tutorial you can learn how to use various MySQL query in the database.
Read More