Instacompute – CentOS – MySQL

Installing MySQL is rather easy on Centos, its already installed If you still wish to install it then
sudo yum install mysql-server

Start the MySQL server
/etc/init.d/mysqld start


You should now see the following messages on your screen
Initializing MySQL database: Installing MySQL system tables...
OK
Filling help tables...
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h i-8941-30081-VM password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

This will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers.

You can start the MySQL daemon with:
cd /usr
/usr/bin/mysqld_safe

You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test
perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
[ OK ]
Starting MySQL: [ OK ]


During the first run MySQL creates a sample database and populates it with data, to remove this and secure your installation
$/usr/bin/mysql_secure_installation
For current passwd press enter and then answer yes to all questions.


To configure launching mysl at boot
/sbin/chkconfig mysqld on


To run the MySQL shell
/usr/bin/mysql -u root -psrid1307*


Setting the root password from the shell
UPDATE mysql.user SET Password = PASSWORD('password') WHERE User = 'root';
FLUSH PRIVILEGES;


Checking users
SELECT User, Host, Password FROM mysql.user;


Some quick SQL commands
Create Db
CREATE DATABASE demodb;
SHOW DATABASES;


To Add a DB user
INSERT INTO mysql.user (User,Host,Password) VALUES('demouser','localhost',PASSWORD('demopassword'));
FLUSH PRIVILEGES;


Grant database user permissions
GRANT ALL PRIVILEGES ON demodb.* to demouser@localhost;
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'demouser'@'localhost';


Configuring MySQL server on CentOS
ask mysqld where it looks for its config

/usr/libexec/mysqld --help --verbose

You get a lot of text. Look for theis line (or a part of it)
Default options are read from the following files in the given order:
/etc/my.cnf ~/.my.cnfq


nano /etc/my.cnf

You will see a line
user=mysql
change mysql to root remember you dropped all the users in the beginnig of this tutorial

My SQL is listening to the default port and so you wont see these entreis
[client]
port = 3306

[mysqld]
port = 3306

“bind-address” value is also missing so add
bind-address = 127.0.0.1
to the [mysqld] section

Lets make mysql start automatically whenever the server starts
chkconfig mysqld on

mysqld and mysqld_safe

mysqladmin

Backups
By default MySQL creates a directory for each database in its data directory:
/var/lib/mysql

Copy this file after making the file readonly as below

mysql -u root -p -e "FLUSH TABLES WITH READ LOCK;"
mysql -u root -p -e "UNLOCK TABLES;"


To run the backup from a script
mysql -u root -p"password" -e "FLUSH TABLES WITH READ LOCK;"
mysql -u root -p"password" -e "UNLOCK TABLES;"


mysqldump
mysqldump -u root -p demodb > dbbackup.sql
mysql -u root -p demodb < dbbackup.sql


Database engine
SHOW TABLE STATUS FROM demodb;


Choosing an engine. MyISAM is default to switch to InnoDB make the following changes to
innodb_buffer_pool_size = 32M
innodb_log_file_size = 8M
innodb_thread_concurrency = 8
innodb_file_per_table


in the [mysqld] section

Manipulating MySQl
——————
mysql -u root -p
CREATE DATABASE databasename;
SHOW DATABASES;
DROP DATABASE databasename;
USE databasename;

INSERT INTO mysql.user (Host,User,Password) VALUES('localhost','demouser',PASSWORD('demopassword'));
SET PASSWORD FOR 'username'@'localhost' = PASSWORD('password');
FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES ON databasename.* TO username@localhost;
FLUSH PRIVILEGES;

FLUSH PRIVILEGES;

SELECT User, Host, Password FROM mysql.user;

DROP USER 'username'@'localhost';
FLUSH PRIVILEGES;

SHOW TABLES FROM databasename;

SELECT COUNT(*) FROM databasename.tablename;

SELECT * FROM databasename.tablename;

REPAIR TABLE databasename.tablename;

OPTIMIZE TABLE databasename.tablename;

DROP TABLE databasename.tablename;

mysqld_safe --skip-grant-tables &

mysql -u root

UPDATE mysql.user SET password=PASSWORD("password") WHERE User='root';
FLUSH PRIVILEGES;