MySQL

From GeilThings

Jump to: navigation, search
MySQL
General
Version 5.5.30
Stable 5.6.12
Function Database
Environment Manager sandbox
SQL SiSQL
Port 3306
Files
Config File /etc/my.cnf
Data File /var/lib/mysql
Log File /var/log/mysqld.log
Scripts
Version Script mysql -V
Start /etc/init.d/mysqld start
Stop /etc/init.d/mysqld stop

Contents

General

Installation

# Using Atomicorp repo (http://www6.atomicorp.com/channels/atomic/centos/6/x86_64/)
wget -q -O - http://www.atomicorp.com/installers/atomic | sh
# Edit the file /etc/yum.repos.d/atomic.repo and set enabled = 0 so only enabling it yum will search in the Atomic repository.
 
yum --enablerepo=atomic install mysql mysql-server
 
/etc/init.d/mysqld start
 
# Set root password, remove anonymous users, remove test database, only local access.
/usr/bin/mysql_secure_installation
 
# Set startup runlevel.
# Add mysqld to chkconfig if not there yet. Either
chkconfig --add mysqld --level 345 mysqld on
# or below if it is already in chkconfig.
chkconfig --level 345 mysqld on
# It means, the service mysqld will be restarted at levels 3, 4, and 5.
# More here: http://www.centos.org/docs/5/html/Installation_Guide-en-US/s1-boot-init-shutdown-sysv.html
# Runlevels (Wikipedia): http://en.wikipedia.org/wiki/Runlevel

Configuration

 

Running

Commands:

CREATE DATABASE databasename;

GRANT ALL PRIVILEGES on databasename.* to 'username'@'127.0.0.1' IDENTIFIED BY 'supersecretpassword' WITH GRANT OPTION;

SHOW GRANTS FOR 'juanitoelcaminante'@'localhost';

REVOKE INSERT ON *.* FROM 'root'@'localhost';

List database Users: SELECT user,host from mysql.db where db = "databasename";

Some tests:

mysql -u <user> -p<password>
show databases;
# No test database
 
create database Test;
use Test;
GRANT SELECT on Test.* to 'readeruser'@'127.0.0.1' IDENTIFIED BY 'megapassword';
create table HelloWorld (mysqlkey varchar(50) not null primary key, language varchar(50), message_text varchar(255));
 
INSERT INTO HelloWorld VALUES ( "Java", "Java", "Hello World from Java, Tomcat, and MySQL");
INSERT INTO HelloWorld VALUES ( "node.js", "node.js", "Hello World from node.js and MySQL");
INSERT INTO HelloWorld VALUES ( "php", "PHP", "Hello World from PHP and MySQL");
INSERT INTO HelloWorld VALUES ( "perl", "Perl", "Hello World from Perl and MySQL");
INSERT INTO HelloWorld VALUES ( "scala", "Scala", "Hello World from Scala, Tomcat, and MySQL");
INSERT INTO HelloWorld VALUES ( "python", "Python", "Hello World from Python and MySQL");
INSERT INTO HelloWorld VALUES ( "ruby", "Ruby", "Hello World from Ruby and MySQL");
 
UPDATE HelloWorld SET message_text="Hello World from Java, Tomcat, and MySQL" WHERE mysqlkey="Java";
 
quit;

Backup MySQL

  • mysqldump -u root -p --add-drop-table databasename >> filename
  • To restore the database: mysql -u username -p databasename < dump_file_name

Upgrade MySQL


APPNAME=mysql
MYSQLADMINUSER=mysqladminuser
MYSQLADMINUSERPASSWORD=mysqladminpassword
MYSQLLOG=/var/log/mysqld.log
 
# Determine current mysql version.
RAWVERSION=`mysqladmin -Version`
# mysqladmin  Ver 8.42 Distrib 5.5.21, for Linux on x86_64
 
# Get Version as Substring.
# Using grep to sed to extract STRING between first occurrence of MATCH1 and next occurrence of MATCH2:
# http://stackoverflow.com/questions/4392106/sed-extract-string-between-first-occurrence-of-match1-and-next-occurrence-of-m
OLDVERSION=`echo "$RAWVERSION" | grep -Po '^.*?\K(?<=Distrib ).*?(?=,)'`
 
# Check.
echo $OLDVERSION
 
RAWNEWVERSION=`curl -sd "action=ask&query=[[MySQL]]|%3FStable&format=json" http://www.geilthings.com/api.php` 
# Get Version as Substring.  
# Using grep to sed to extract STRING between first occurrence of MATCH1 and next occurrence of MATCH2:  
# http://stackoverflow.com/questions/4392106/sed-extract-string-between-first-occurrence-of-match1-and-next-occurrence-of-m  
NEWVERSION=`echo "$RAWNEWVERSION" | grep -Po '^.*?\K(?<=Stable\":\[\").*?(?=\")'`  
echo $NEWVERSION
 
if [[ ${OLDVERSION} != ${NEWVERSION} ]]; then
  echo ${APPNAME}: installed: ${OLDVERSION}, actual: ${NEWVERSION}
  echo "You could update ${APPNAME} to ${NEWVERSION}"
  echo "Would you like to install ${APPNAME} ${NEWVERSION} y/n?"
  read item
  if [[ "$item" == "y" || "$item" == "Y" ]]; then
    echo "Installing ${APPNAME} ${NEWVERSION}. Follow instructions."
  else
    echo "Exit"
  fi
else
  echo "You don't need to update ${APPNAME}; it is uptodate (${NEWVERSION})"
fi
 
DATE=`date +%Y-%m-%d`
 
# The script breaks if variable names are not enclosed with {}
BACKUPDIR=/home/backup/${APPNAME}/${APPNAME}_${OLDVERSION}_${DATE}
echo ${BACKUPDIR}
 
# Create backup directory if it does not exist.
if [ ! -d "${BACKUPDIR}" ]
then
    mkdir -m 755 -p ${BACKUPDIR}
fi
 
# Backup data.
 
cd ${BACKUPDIR}
mysqldump -u ${MYSQLADMINUSER} -p${MYSQLADMINUSERPASSWORD} --add-drop-table --all-databases >> mysqldatabases.sql
cp /etc/my.cnf ${BACKUPDIR}/my.cnf
 
# Shut down MySQL.
service mysqld stop
 
# Check that the server in use is not out of sync, e.g. see here
# http://atomicorp.com/forums/viewtopic.php?f=1&t=6291&hilit=mysql
# for www7.atomicorp.com out of sync in October 2012.
# To force yum to use only a particular server, comment the mirror list, uncomment the base url.
# e.g. baseurl = http://www6.atomicorp.com/channels/atomic/centos/6/x86_64
 
# Mirror List:
# http://www.atomicorp.com/mirrorlist/atomic/centos-6-x86_64
 
yum --enablerepo=atomic upgrade mysql mysql-server mysql-devel mysql-libs
 
service mysqld start
mysql_upgrade -u ${MYSQLADMINUSER} -p${MYSQLADMINUSERPASSWORD}
 
# Check version.
mysqladmin -Version
 
# Check log file.
tail -30 ${MYSQLLOG}
 
# Check applications.
# ...

Programming MySQL


Versions

Software name Version number Version date
MySQL 5.5.29
5.5.30
8 January 2013
5 February 2013

Comments

blog comments powered by Disqus