MySQL


  • Here are my notes for MySQL on CentOS 5.


Install MySQL


  • Install mysql:

    • sudo apt-get install mysql-server (Ubuntu)
    • yum install mysql mysql-server (CentOS)
  • For cluster systems:
    • Move mysql data folder to shared storage:

      • mv /var/lib/mysql /data/mysql

      • ln -s /data/mysql /var/lib

  • For non-clustered system, start mySQL on boot:
    • chkconfig --level 345 mysqld on
  • Start mysql service. Note that on ubuntu, the service name is "mysql" but on CentOS it is "mysqld":

    • service mysqld start


Set initial root password

  • Run script:

    /usr/bin/mysql_secure_installation
    • Press ENTER when asked for current mysql root password
    • Answer "Y" to "set root password?"
    • Enter mysql root password
    • Answer "Y" to "remove anonymous users"
    • Answer "Y" to "disallow root login remotely"
    • Answer "Y" to "remove test database and access to it"
    • Answer "Y" to "reload privilege tables now"
Connecting as administrator
  • # mysql --user=root --pass mysql

Recover Forgotten MySQL root Password
  • As root, stop mySQL process:
    • service mysql stop
  • Start mysqld with option:
    • mysqld --skip-grant-tables &
  • Connect ot mySQL:
    • mysql -u root mysql
  • Change password for mySQL root user:
    • UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root';
    • FLUSH PRIVILEGES;
    • exit

Typical Administrator Commands


  • Create Database:


    • CREATE DATABASE dbname;
  • Creating new user
    • CREATE USER app_user;
    • GRANT ALL PRIVILEGES ON dbname.* TO app_user@localhost;
    • SET PASSWORD FOR app_user@localhost = OLD_PASSWORD('app_password');
    • FLUSH PRIVILEGES;
  • List databases:
    • show databases;

Change to a different database:

  use db;

List tables in current database:

  show tables;

Show schema:

  desc tablename;

Alt. Create database

mysqladmin -u app_user -p create dbname

Alt. Drop database

mysqladmin -u app_user -p drop dbname

Backup/Restore Database
  • Backup database:
    • mysqldump -u root -p bugtracker > bugtracker.sql
  • Restore database:
    • create the owner and database first
    • ???