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
