database server campus-booster id : ****** copyright © supinfo. all rights reserved mysql
TRANSCRIPT
![Page 1: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/1.jpg)
Database serverCampus-Booster ID : ******
www.supinfo.com
Copyright © SUPINFO. All rights reserved
MySQL
![Page 2: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/2.jpg)
Your trainer…
Title: **Enter title or job role.
Accomplishments: **What makes the presenter qualified to present this course.
Education: **List degrees if important.
Publications: **Writings by the presenter on the subject of the course or presentation.
Contact:**Campus-Booster ID: [email protected]’s Name
MySQL
![Page 3: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/3.jpg)
Course objectives
Install Mysql. Server installation and configuration
Use Mysql
Replicate Mysql
By completing this course, you will:
MySQL
![Page 4: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/4.jpg)
Course topics
Introduction. MySQL History and concepts
Configuration. client-server installation and configuration
MySQL Usage.
Course’s plan:
MySQL
![Page 5: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/5.jpg)
Introduction
What’s MySQL ?
MySQL
![Page 6: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/6.jpg)
Preview
History
What’s MySQL
Comparison
Introduction
![Page 7: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/7.jpg)
History
First version : May,23 1995
Created by Michaël Widenius in Sweden
Under GPL License with the version 3
In June 2000
Redeem by Sun Microsystems
FOSS (Free and Open Source Software)
Commercial License
Current version 5.1
Introduction
![Page 8: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/8.jpg)
What’s MySQLMySQL is a RDBMS
MySQL is a relational SQL database server OpenSource SoftwareMulti OS :
•Linux
•Mac OS X
•WindowsThe most used DBMS all over the worldLightweight resources neededCluster supported with the Version 4.1
Introduction
![Page 9: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/9.jpg)
Comparaison
The different versions of MySQL
Features MySQL 3.x
No
No
No
No
No
MySQL 4.0
Partial
No
No
No
No
MySQL 5.x
Yes
Yes
Yes
Yes
Yes
Subquery
View
Foreign Keys
Triggers
Constraints
Introduction
![Page 10: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/10.jpg)
Stop and thinkIntroduction
Do you have any questions?
![Page 11: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/11.jpg)
Configuration
Configuration of MySQLv5
MySQL
![Page 12: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/12.jpg)
Preview
Installation
Configuration
Configuration
![Page 13: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/13.jpg)
Installation
Installation with binary
Recommended
Download it :
http://dev.mysql.com/downloads/
Installation with packages tools
Needs to update the sources list
Configuration
![Page 14: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/14.jpg)
Installation
Launch the server
Use the automatic rc script
Manual Launch :
mysqld_safe usage comparative to mysqld:
In case of problems reboot the server
logging
Give parameters to mysqld
Configuration
# /etc/init.d/mysqld start# /etc/init.d/mysqld start
# /usr/bin/mysqld_safe start# /usr/bin/mysqld_safe start
![Page 15: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/15.jpg)
Configuration
Only one configuration file
my.cnf
For the server like for the client
Based on a section structure
Example of a configuration file :
Configuration
![Page 16: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/16.jpg)
Stop and thinkConfiguration
Do you have any questions?
![Page 17: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/17.jpg)
Basic usage
Basic usage of MySQL 5
MySQL
![Page 18: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/18.jpg)
Preview
Define an admin password
Manage users
Create/Delete a database
Administration tools
Basic usage
![Page 19: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/19.jpg)
Define a root password
By default the root password is empty
Adding a password for the root account :
or with mysqladmin command:
An user exists once he has rights on a table
# mysql -u root
mysql> GRANT ALL PRIVILEGES ON *.* TO
'root'@'localhost' IDENTIFIED BY
'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
# mysql -u root
mysql> GRANT ALL PRIVILEGES ON *.* TO
'root'@'localhost' IDENTIFIED BY
'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
Basic usage
# mysqladmin -u root password <root_password># mysqladmin -u root password <root_password>
![Page 20: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/20.jpg)
Listing existing usersBasic usage
A simple SQL statement :
mysql> SELECT user,password,host FROM mysql.user;
+---------+------------+-------------------+
| user | password | host |
+---------+------------+-------------------+
| root | *A4E990E4… | localhost |
| root | | Arbalest |
| supinfo | *06CDF16C… | linux.supinfo.com |
+---------+------------+-------------------+
3 rows in set (0.01 sec)
mysql> SELECT user,password,host FROM mysql.user;
+---------+------------+-------------------+
| user | password | host |
+---------+------------+-------------------+
| root | *A4E990E4… | localhost |
| root | | Arbalest |
| supinfo | *06CDF16C… | linux.supinfo.com |
+---------+------------+-------------------+
3 rows in set (0.01 sec)
![Page 21: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/21.jpg)
Create a database
With mysqladmin
Within MySQL client (SQL statement)
# mysqladmin -p create mandriva # mysqladmin -p create mandriva
mysql> CREATE DATABASE mandriva;mysql> CREATE DATABASE mandriva;
Basic usage
![Page 22: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/22.jpg)
Delete a database
With mysqladmin
Within MySQL client (SQL statement)
Basic usage
# mysqladmin -p drop mandriva# mysqladmin -p drop mandriva
mysql> DROP DATABASE mandriva;mysql> DROP DATABASE mandriva;
![Page 23: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/23.jpg)
Note
Every
command in
basic usage is
a SQL
statement
Basic usage
![Page 24: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/24.jpg)
Administration tools
Many MySQL tools are available:
Installed with the server
mysql_install_db, mysql_find_rows, mysql_manager
Non-free Clients
MySQL explorer, MySQL tracer
Web Clients
PHPMyAdmin
Basic usage
![Page 25: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/25.jpg)
Stop and thinkBasic usage
Do you have any questions?
![Page 26: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/26.jpg)
Replication
Setting up replication with MySQL
MySQL
![Page 27: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/27.jpg)
Preview
What’s a replication?
Setting up the master
Setting up the slave(s)
Replication
![Page 28: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/28.jpg)
Overview
Setting up a replication
Characteristics:
Unidirectional: Master -> Slave(s)
Binary log file usage
Element of concern:
The database
What for?
Fault tolerance
Load balancing
Replication
![Page 29: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/29.jpg)
Replication account
Replication user creation
Rights attribution replication slave
Command used GRANT statement
Replication
mysql> GRANT replication slave,replication client,
super,reload ON *.* TO 'user'@'%'
IDENTIFIED BY 'password'
mysql> GRANT replication slave,replication client,
super,reload ON *.* TO 'user'@'%'
IDENTIFIED BY 'password'
![Page 30: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/30.jpg)
Setting up the master
Configuration of the master and database archiving
Configuration
Adding options
• log-bin: Active the creation of binary log
• server-id: master’s unique number
Command used for manual replication
Replication
# cd /var/lib/mysql# tar -cvjf mysql-database.tar.bz2 test# cd /var/lib/mysql# tar -cvjf mysql-database.tar.bz2 test
![Page 31: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/31.jpg)
Setting up the slave(s)
Slave configuration
Configuration
Adding options
• master-host: master server name
• master-user: replication account username
• master-password: replication account password
• master-port: server port
• server-id: slave’s unique number (mandatory)
Replication
![Page 32: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/32.jpg)
Setting up the slave(s)
Activate the replication (after creating the same database on both servers)
Command :
Replication
Mysql > LOAD DATA FROM MASTER;Mysql > LOAD DATA FROM MASTER;
![Page 33: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/33.jpg)
Setting up the slave(s)
Uncompressing the database
Command for uncompressing
Replication
# cd /var/lib/mysql
# tar -xvjf mysql-database.tar.bz2
# cd /var/lib/mysql
# tar -xvjf mysql-database.tar.bz2
![Page 34: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/34.jpg)
Note
The
replication is
done, you
just have to
restart the
master and
the slave(s)
Replication
![Page 35: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/35.jpg)
Stop and thinkReplication
Do you have any questions?
![Page 36: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/36.jpg)
MySQL setup
Basic usageMySQL
Replication
Course summary
Installation of MySQL
MySQL
![Page 37: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/37.jpg)
For more
CoursesPublications
Web sites
Introduction to databases
If you want to go into these subjects more deeply…
www.labo-linux.com
www.dunod.fr
MySQL
![Page 38: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/38.jpg)
Congratulations
You have successfully completed the SUPINFO course module
MySQL
![Page 39: Database server Campus-Booster ID : ****** Copyright © SUPINFO. All rights reserved MySQL](https://reader036.vdocuments.us/reader036/viewer/2022062409/5697c0071a28abf838cc6143/html5/thumbnails/39.jpg)
The endMySQL