Backup and Restore a table

Backup

mysqldump -u -p db_name table_name > script_name.sql

Example :  mysqldump -u dse -p dse_db tread_data_2015 > tread_data_2015.sql


Restore

Loggin to mysql : mysql -u <user_name> -p db_name

execute the script: mysql> source <full_path>/table_name.sql

Example: mysql -u dse -p dse_db
mysql> source /u0/SQL_SCRIPTS/tread_data_2015.sql

Create USER

Create a user for localhost

CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password';

--- Give all privileges to user
GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'localhost' WITH GRANT OPTION;

-- grant all operation on a database to user

GRANT ALL ON db_name.* TO 'user_name'@'localhost';


Create a user for other remote hosts

CREATE USER 'user_name'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' WITH GRANT OPTION;

MySQL IN ONE DAY - Hour #1

Character Set:all avilable character set : mysql> show character set;
show current default character set: mysql> select charset('A');

Change Default Database Character Set:
character set of a database:
mysql>ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_persian_ci ;

character set of a table :
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_persian_ci;

Taking Dump :

Please go to the mysql's bin directory 'C:\mysql\bin' and runthe dump command

mysqldump --opt -u USERNAME -p DATABASE_NAME > "file_name.sql"

With Data
mysqldump -u user_name -ppassword db_name > file_name.sql
Without Data
mysqldump --no-data -u user_name -ppassword db_name > file_name.sql

Windows :

C:>cd mysql\bin
C:\mysql\bin> mysqldump -u root -p employee_database > "C:/employee.sql"

Linux :

#cd mysql/bin
~home/mysql/bin> ./mysqldump -u root -p password employee_database > "/u0/employee.sql"


Applying Dump :

>mysql -u root -p

Create the database

mysql> create database employee_database;


Now run the .sql scripts

go to the mysql nin directory and execute the command

mysql -u root -p password employee_database < style="font-weight: bold;">


ALTER TABLE :

1. To drop a default Constraint of a column : ALTER TABLE table_name ALTER column_name DROP DEFAULT;
2. To modify a column : ALTER TABLE table_name MODIFY column_name (column definition) ;