What is MySQL if you don't already know?
Installing MySQL Quick Install
Download mysql-MYSQLVER.tar.gz
# cd /usr/local/src
# tar -xvzf /pathto/mysql-MYSQLVER.tar.gz
# cd mysql
Run configure --help to see other options
# ./configure --prefix=/usr/local/mysql
# make && make install
# /usr/local/mysql/bin/mysql_install_db
# cd /usr/local/mysql
# adduser mysql
# chown -R mysql.mysql var/
# cd bin
Copy the mysql startup script to /usr/local/mysql/bin
and symlink it to /etc/rc.d/init.d/mysql
# cp /usr/local/src/mysql /support-files/mysql.server .
# chmod +x mysql.server
# ln -s /usr/local/mysql/bin/mysql.server /etc/rc.d/init.d/mysql
Have mysql start durning boot time (and shutdown when shutting down)
# chkconfig mysql
Search for =root change the username to mysql
# vi /usr/local/mysql/bin/safe_mysqld
/=root
d 4 -->
i mysql
<esc>
:wq
# /usr/local/mysql/bin/mysqladmin -uroot password 'newpassword'
Script Install
#!/bin/sh
displaynotice() {
echo -e $1
}
MYSQLVER=3.23.27-beta
function installmysql() {
displaynotice "Setting up MySQL...\n"
wget -U lynx
http://mysql.com/Downloads/MySQL-3.23/mysql-$MYSQLVER.tar.gz >/dev/null
2>&1
if [ -f mysql-$MYSQLVER.tar.gz ]; then
displaynotice "mysql-$MYSQLVER.tar.gz was downloaded..\n"
tar -xvzf mysql-$MYSQLVER.tar.gz
displaynotice " Untared MySQL...\n"
cd mysql-$MYSQLVER
./configure --prefix=/usr/local/mysql >/dev/null 2>&1
displaynotice " Configured MySQL ...\n"
make >/dev/null 2>&1
displaynotice " Compiled MySQL ...\n"
make install >/dev/null 2>&1
cd scripts
./mysql_install_db >/dev/null 2>&1
displaynotice " MySQL default databases created ...\n"
cd ../
cp support-files/mysql.server /etc/rc.d/init.d/mysql
chmod +x /etc/rc.d/init.d/mysql
displaynotice " mysql.server copied and made executable ...\n"
else
displaynotice "MySQL version $MYSQLVER download failed...\n"
fi
}
installmysql
MySQL Administration using the command lines tools available to you. mysql mysqladmin mysqldump mysqlaccess
Changed the root password after you install the software.... # mysqladmin -uroot password 'newpassword' # Example commandline # # CREATE DB: mysqladmin create databasename -p # VIEW DBs : mysql -e 'SHOW DATABASES' -p # SELECT DATA : mysql databasename -e 'SELECT * from user WHERE User = "root"' # Backing UP DB: mysqldump --opt databasename > /path/to/dbbackups/dd-mm-yy.database.mysql # CREATE a NEW DB from and OLD one: # mysqladmin create newdb -p # mysqldump --opt olddb -p | mysql newdb -p # SQL statement in a text file: mysql databasename < SQLstatement_file.mysql -p # LOOK at DB structure: mysqlshow databasename -p # mysqlshow databasename tablename -p # DROP olddb: mysqladmin -p drop databasename # Status of DB server: mysqladmin status -p # SHUTDOWN DB: mysqladmin -p shutdown # DOING Increment backups: run the mysqld with the --log-updatemysql: Allows direct connections to the database server, allowing queries via the mysql prompt mysql database -p mysql> mysql> SELECT * FROM table; mysql> SHOW DATABASES;
mysqladmin : create databasename Create a new database drop databasename Delete a database and all its tables extended-status Gives an extended status message from the server flush-hosts Flush all cached hosts flush-logs Flush all logs flush-status Clear status variables flush-tables Flush all tables flush-threads Flush the thread cache flush-privileges Reload grant tables (same as reload) kill id,id,... Kill mysql threads password new-password Change old password to new-password ping Check if mysqld is alive processlist Show list of active threads in server reload Reload grant tables refresh Flush all tables and close andlogfiles shutdown Take server down status Gives a short status message from the server variables Prints variables available version Get version info from server The arguments for the mysqladmin util can be executed from the mysql prompt example: mysqladmin processlist -p mysqladmin create dbname mysqladmin drop dbname from the mysql prompt mysql -p mysql> show processlist; mysql> create database DBNAME; mysql> drop database DBNAME; If you desire you can write shell scripts to automate the cli commands. SHELL Script example
#!/bin/sh
# Filename: backup.sh
# Function: do daily backups of databases
# updated
# -- added changelog to this file
# Change To fit your system
userid=root
# On my development system I don't have the root password
# You will also need add -p${password} to
#password=passwd
# Create a cron job to run every night at 4:30am
# 30 04 * * * /patch/to/pccs_mysqladm/bin/backup.sh
# Location of Mysql bin directory
mysqlbinclient="/usr/local/bin/mysql -u${userid}"
mysqlbindump="/usr/local/bin/mysqldump -u${userid}"
# Location of Mysql Database backup directory
dbbackupdir=/home/www/htdocs/pccsmysqladm/dumpfiles
$mysqlbinclient -e 'SHOW DATABASES' | sed 's/|//g' | grep -v 'Database' \
| while read dbname; do
if [ -d $dbbackupdir/${dbname}_db ]; then
echo -n 2>/dev/null
else
mkdir $dbbackupdir/${dbname}_db
fi
curr_time=`date | sed 's/ //g' | sed 's/:/_/g'`
$mysqlbindump --opt ${dbname} > $dbbackupdir/${dbname}_db/${curr_time}.${dbname}_dumpsql.BACKUP 2>/dev/null
done
PERL example
#!/usr/bin/perl
use strict;
my @now = localtime;
my $day = $now[3];
my $year = $now[5] + 1900;
my $mon = $now[4]+ 1;
my @databases = qw(mysql test);
if($day < 10 ) { $day = "0$day" }
if($mon < 10 ) { $mon = "0$mon" }
print "Backing up files for $year$mon$day\n";
for (@databases) {
print "$_\n";
system("/usr/local/bin/mysqldump $_ -u userid -ppasswd >
/home/archive/mysqlbackups/$year$mon$day-$_.mysql");
}
|
