ID | FIRSTNAME | LASTNAME | GENDER | JOB | JOININGDATE | SALARY | CITY | PHONE | |
---|---|---|---|---|---|---|---|---|---|
1 | Purushotham | Karimbedu | M | QA | 01-Oct-2012 | 30000 | Hyderabad | 9000109120 | |
2 | |||||||||
Get a list of MySQL user accounts:-
mysql> SELECT User FROM mysql.user;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Get a list of Database user accounts:-
mysql > select * from users;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Login in my sql with linux terminal :-
root@chinna:~# mysql -u root -p
Enter password:*******
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Create database on mysql server:-
mysql> create database techlearn;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
List/show/view all databasenames on mysql server :-
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| techlearn |
| abc |
| xyz |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Select/use to a database:-
mysql> use databasename;
ex:
mysql> use techlearn;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Create table:-
mysql> create table employee(id integer,name varchar(15),gender varchar(5),salary integer);
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Enter values into table :-
mysql> insert into employee values(1, 'Purushotham K', 'Male',25000);
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Adding a column in table :-
mysql> alter table employee add(email varchar(25));
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Update a column in table :-
mysql> update employee set email='chinna4u.kp@gmail.com' where id=1;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
See all the table from a database :-
mysql > show tables;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
See all the tables field formates or description of table :-
mysql > describe tablename;
ex:
mysql> describe users;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Delete / Drop the database from mysql server:-
mysql > drop database databasename;
Ex :-
mysql > drop database techlearn;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Delete / Drop the table name:-
mysql > drop table tablename;
Ex :
mysql> drop table cache;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Importing a SQL db into mysql using the terminal :-
root@chinna:~# mysql -u username -p password databasename < filename.sql
Ex:
root@chinna:~# mysql -u root -p techlearn < /home/chinna/Desktop/techlearnin.sql
Password : *******
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Exporting a SQL db into mysql using the terminal :-
root@chinna:~# mysqldump -u root -p databasename > databasename.sql
Enter password: *******
Ex:
root@chinna:~# mysqldump -u root -p techlearn > techlearn.sql
Password : *******
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Clear screen :-
mysql > Ctrl+l;
or
mysql > system clear;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Unblocking an account :-
Say you accidentally "block" your admin account and cannot log in. From within a MySQL client you can run this command to unblock it.
mysql > update users set status = 1 where uid = 1;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Reset user/root password :-
Find a way to reset my root level password in Drupal 7 with only access to phpMyAdmin and ftp.
mysql--> database--> users --> edit users --> add below password hash code in pass insted of old code field.
password hash code : $S$C6x2r.aW5Nkg7st6/u.IKWjTerHXscjPtu4spwhCVZlP89UKcbb/
New pwd : NEW_TEMP_PASSWORD
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Import large .sql files to phpmyadmin in LAMP :-
root@chinna:~# nano /etc/php5/apache2 /php.ini
Find:
post_max_size = 8M
upload_max_filesize = 2M
max_execution_time = 30
max_input_time = 60
memory_limit = 8M
Change to:
post_max_size = 750M
upload_max_filesize = 750M
max_execution_time = 300
max_input_time = 540
memory_limit = 1000M
Save the changes.
root@chinna:~# /etc/init.d/apache2 restart
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
How to connect cPanel-SSH Access :-
root@debian:/home/chinna# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
root@debian:/home/chinna# cat ~/.ssh/id_rsa.pub
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDP3xeohPxX0BkT2NRGC69+5C7wrkJ2UeDJVp6z4H3yEA+tcNqZK0pjp4JO+vKXMiYsKwEmccDEoxs1cUBQf4GX9T5OzQVcHgpy7NZ/kPtcwzn//WdUECvlY5KQG30Iq3EdEn91d90kcRdU+h4sewkb5y20Z7i7YE5xi5qrJGDiPyMZMo0HJfe6N1SsAd5qj3Xis1qxnruUpXEtgfF8NbSw8Z7xEW5 root@debian
root@debian:/home/chinna# cat ~/.ssh/id_rsa
-----BEGIN RSA PRIVATE KEY-----
MIIEpAIBAAKCAQEAz98XqIT8V9AZFfA7dAjBdqCwuwxQot5lLWHHB8L3CZN7XM5P
.
5syVzV03oeUiCxA5gySakm+dvp2RSGjWd59rOmqMzO8CDIYPJCx6Ew==
-----END RSA PRIVATE KEY-----
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Resetting a lost MYSQL root password :-
stop Mysql
root@chinna:/home/chinna# /etc/init.d/mysql stop
safe Mode
we need to start Mysql in safe mode-that is to say we will start Mysql but skip the user privileges table.
root@chinna:/home/chinna# sudo mysqld_safe –skip-grant-tables &
Login
now we need to log into Mysql and set the password
root@chinna:/home/chinna# mysql -u root
note:No password is required at this stage as when we started Mysql we skipped the user privileges table.
Next ,instruct Mysql which database to use:
root@chinna:/home/chinna# use mysql;
Reset Password
Enter the new password for the root user as follows:
root@chinna:/home/chinna# update user set password=PASSWORD(“newpassword”) where User='root';
and finally,flush the privileges:
root@chinna:/home/chinna# flush privileges;
now restart the mysql by logging out
root@chinna:/home/chinna# quit
root@chinna:/home/chinna# sudo /etc/init.d/mysql stop
root@chinna:/home/chinna# sudo /etc/init.d/mysql start
Login
root@chinna:/home/chinna# mysql -u root -p