database

Q. Remove multiple database at a time
DB_START_WIDTH="workflow"
MUSER="root"
MPASSWORD="root123"
MYSQL="mysql"
DBS="$($MYSQL -u$MUSER -p$MPASSWORD -Bse 'show databases')"
echo $DBS

for db in $DBS; do 
    if [["$db" =~ "^${DB_START_WIDTH}"]]; then 
	echo "deleting $db"
	$MYSQL -u$MUSER -p$MPASSWORD -Bse "drop $db" 
    fi
done


Q. Recover database password
$ sudo service mysql stop
$ sudo mysqld_safe --skip-grant-tables &
$ mysql -u root
sql> flush privileges;

Q. mysql data dir
A. datadir = /var/lib/mysql

Q. how to recover database delete
i) Add the following line in your /etc/mysql/my.cnf file
  log_bin = /var/log/mysql/mysql-bin.log
  max_binlog_size = 100M
  
ii)stop your mysql server
   $ sudo service mysql stop
 
iii)start your mysql server
   $ sudo service mysql start
  
iv) copy /var/log/mysql-bin.00001 to one file 
    mysqlbinlog /var/log/mysql/mysql-bin.000001 > statement.sql
v) statement.sql file contails all the database operation
   It will show all  

Q. why use bind-address
A. mysql is lisiting on the address by default is it listing localhost(127.0.0.1)
If you want to listin all ip address of your server comment out or remove bind address   

Q. why used  netstat -an 
A. show list of tcp port used by 

Q How to load default database (mysql and schema)
A. mysql_install_db

Q how to mysql user change
A. update mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;


Q. How to remove database
A. sudo apt-get --purge remove mysql-server mysql-common mysql-client

Q. procedure show
A. SHOW PROCEDURE STATUS;

Q. #<ActiveRecord::StatementInvalid: Mysql2::Error: PROCEDURE pacerpro_1.5.usp_AddCICMS_Synonyms does not exist: CALL usp_AddCICMS_Synonyms(48903, 17, @vResultCode)>


mysql> SHOW PROCEDURE STATUS;
+----------------+------------------------------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db             | Name                         | Type      | Definer | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+----------------+------------------------------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| pacerpro_alpha | usp_AddCICMS_Synonyms        | PROCEDURE | root@%  | 2012-06-20 18:57:46 | 2012-06-20 18:57:46 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |


Q. how to connect database server in different host 
A. mysql -u root -p --host=host_name

Q. How do you create database from command prompt
A. mysql -u roo -p -e "create dtabase testing"