MySQL Database & Basic Commands

Install MySQL Packege : -

yum install httpd mysql mysql-server -y

mysqlcheck -u root -p --check --optimize --all-databases

service mysqld start

chkconfig mysqld on

mysql_secure_installation

Enter current password for root (enter for none): press enter

give password

yes

yes

yes

Access Database : --

mysql -u root -p

mysql -u root -p

show databases;

create database name;

use name;

\q

/etc/init.d/mysqld restart

service mysqld restart

chkconfig mysqld on

Database Backup :-

mysqldump -uroot -ps23S32EG&^ bestgladiator > mysql.sql

Database Restore

mysql -u root -pJHYfgrXWL nelsonm < nelsonm.sql

top -u mysql

    

use databasename;

select * from email_lists;

MYSQL Update Query

Mysql Tuning :

Put following lines in /etc/my.cnf after user=mysql no spaces & blank lines

     innodb_log_file_size = 32M

     innodb_buffer_pool_size = 2048M

     innodb_log_buffer_size = 4M

     innodb_flush_log_at_trx_commit=2

     basinnodb_thread_concurrency=8

service mysqld restart

if get login issue in interspire after this

cd /var/lib/mysql

mv ib_logfile0  1ib_logfile0

mv ib_logfile1 1ib_logfile1

/etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

innodb_log_file_size = 64M

innodb_buffer_pool_size =1024M

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit=2

innodb_thread_concurrency=8

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

#############  motofox is new..... greenatonn is old  ###############

select * from email_lists;

select * efrom email_lists limit 5;

UPDATE `email_lists` SET  `bounceemail` = 'bounce@wallstrilty.org';

UPDATE `email_lists` SET  `ownername` = 'info';

UPDATE `email_lists` SET  `bounceusername` = 'bounce.mindgolerme.com';

UPDATE `email_lists` SET `replytoemail` = 'info@mindgolerme.com' WHERE `replytoemail` = 'info@avanilograna.com';

UPDATE `email_lists` SET `bounceemail` = 'bounce@mindgolerme.com' WHERE `bounceemail` = 'bounce@avanilograna.com';

UPDATE `email_lists` SET `owneremail` = 'info@mindgolerme.com' WHERE `owneremail` = 'info@avanilograna.com';

---------- -------------------------------------

after login

SELECT emailaddress INTO OUTFILE 'getcopbhupendra.csv' FROM email_banned_emails;

direct from termital

mysql -uroot -pEdNzRT -e "show databases;"

mysql -uroot -pEdNzRT nopenjohn -e "show tables;" | grep banned

    

mysql -uroot -pEdNzRT nopenjohn -e "select emailaddress from email_banned_emails;" > supression.csv

mysql -uroot -pPasswd "DBname" -e "select emailaddress from email_list_subscribers where listid=10;" > listname.csv

Delete Email lists #######################3

use database;

DELETE FROM `email_list_subscribers` WHERE `listid` = 19;

DELETE FROM `email_lists` WHERE `listid` = 19;

export contact list

mysql -uroot -pedfgtGolF corwine_mnm -e "select emailaddress from email_list_subscribers where listid=10;" > US_TOTAL.csv

#####################################  garuda MTA add issue #########################################

use database;

ALTER TABLE `mta_group` CHANGE `mta` `mta` LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;

#########################################33

10.10.10.30 mysql hosted server

10.10.10.10 client machine

Step 1. Create a user which is admin of a database

mysql -p

> CREATE USER 'user'@'127.0.0.1' IDENTIFIED BY 'password';

> CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

> GRANT ALL PRIVILAGES ON *.* TO 'user'@'localhost' WITH GRANT OPTION ;

> GRANT ALL PRIVILAGES ON *.* TO 'user'@'127.0.0.1' WITH GRANT OPTION ;

> FLUSH PRIVILAGES;

> FLUSH HOSTS;

> select Host,User from mysql.user;

> quit

# mysql -u user –p password

Step 2. Create a user which is admin of a database

mysql -p

> CREATE USER 'user2'@'10.10.10.10' IDENTIFIED BY 'password';

> GRANT ALL PRIVILAGES ON *.* TO 'user2'@'10.10.10.10' WITH GRANT OPTION ;

> FLUSH PRIVILAGES;

> FLUSH HOSTS;

> select Host,User from mysql.user;

> quit

# mysql -u user -ppassword

vi /etc/my.cnf

write below symblic link

bind-adress=10.10.10.30

Define Port In Selinux :-

vi /etc/sysconfig/iptables

-A INPUT -p tcp -m state --state NEW,STABLISHED -m tcp --dport 3306 -j ACCEPT

service iptables restart

service mysqld restart

From client

#mysql -u user2 -h 10.10.10.30 -p

Change Password of Database

Changepass

mysql -uroot -pfkiasHDY

vim /etc/my.cnf

cd /var/lib/mysql/

mv ib_logfile0 1ib_logfile0

mv ib_logfile1 1ib_logfile1

service mysqld restart

mysql -uroot -pfkiasHDY

exit

cd /var/www/salihundam/admin/import/

ls

#######  CHECK DATABASE SIZE #############

SELECT table_schema AS "Database",

ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"

FROM information_schema.TABLES

GROUP BY table_schema;

vim /etc/ssh/sshd_config

service sshd restart

Backup and restore all database 

#backup all databases in one file (eventually add the option --add-locks):

mysqldump -uroot -p -–all-databases > file.sql

#backup all databases in one gzipped file:

mysqldump -u username -p -–all-databases | gzip > file.sql.gz

#restore all databases:

mysql -u username -p < file.sql