Mainos / Advertisement:

Ero sivun ”Mysql/en” versioiden välillä

Kohteesta Taisto
Siirry navigaatioon Siirry hakuun
(Ak: Uusi sivu: Mysql is a handy database which is easy to manage using the phpmyadmin application. Using phpmyadmin requires HTTP server software as well for instance Apache2 or Ngi...)
(Ak: Uusi sivu: == Sources ==)
 
(44 välissä olevaa versiota samalta käyttäjältä ei näytetä)
Rivi 2: Rivi 2:
 
Mysql is a handy database which is easy to manage using the [[phpmyadmin]] application. Using [[phpmyadmin]] requires HTTP server software as well for instance [[Apache2]] or [[Nginx]] and [[Php5]].
 
Mysql is a handy database which is easy to manage using the [[phpmyadmin]] application. Using [[phpmyadmin]] requires HTTP server software as well for instance [[Apache2]] or [[Nginx]] and [[Php5]].
  
== Asennus ==
+
== Installation ==
  
 
   [[aptitude]] install mysql-server
 
   [[aptitude]] install mysql-server
  
Anna mysql root-käyttäjän kirjautumisen salasana asennuksen yhteydessä. Älä unohda salasanaa. Voit luoda myöhemmin lisää käyttäjätilejä ja vaihtaa salasanoja.
+
Give the mysql root user login password in installation. Do not forget the password. You can create more user accounts and change passwords later.
  
Voit käyttää mysql tietokantaa ilman  [[phpmyadmin | phpmyadminia]] asentamalla
+
You can also use a mysql database without [[phpmyadmin]] but it helps a lot with managing it.
  
 
  [[aptitude]] install mysql-client
 
  [[aptitude]] install mysql-client
  
 
<gallery>
 
<gallery>
Tiedosto:Debian mysql installation 1.png|Anna MySQL palvelimen pääkäyttäjälle salasana
+
Tiedosto:Debian mysql installation 1.png|Give password for MySQL server root user
Tiedosto:Debian mysql installation 2.png|Toista pääkäyttäjän salasana
+
Tiedosto:Debian mysql installation 2.png|Repeat root user password
 
</gallery>
 
</gallery>
  
== MySQL Konfiguraatio ==
+
== MySQL configuration ==
  
Konfigurointi tiedosto löytyy Ubuntussa ja Debianissa:
+
Configuration file can be found in Ubuntu and Debian under:
  
 
   /etc/mysql/my.cnf
 
   /etc/mysql/my.cnf
  
MySQL palvelimen portti jota se kuuntelee. Oletuksena se on 3306.
+
The port the MySQL server listens to. By default it is set to 3306.
  
 
  [client]
 
  [client]
 
  port 3306
 
  port 3306
  
Osoite jota vain kuuntelee, # risuaita eteen niin kuuntelee kaikkia IP-osoitteita.
+
Address the server will listen to. Put a # number sign in front of the line and it will listen to all IP addresses.
  
 
  bind-address              = 127.0.0.1
 
  bind-address              = 127.0.0.1
  
== Komentorivi ==
+
== Command Line ==
  
Mysql tietokantaa voi hallita komentorivillä tai kätevästi [[phpmyadmin]] websivu ohjelmistolla.
+
Mysql databases can also be managed by command line.
  
Voit muodostaa komentorivi näkymään yhteyden:  
+
You can connect to a mysql server with command line by:  
  
 
   mysql -u root -p
 
   mysql -u root -p
  
Anna mysql root käyttäjän salasana
+
Give the mysql root user password.
  
=== Käyttäjätilit ===
+
=== User Accounts ===
  
Uuden kättäjätilin voit luoda komennolla:
+
List user accounts:
 +
 
 +
  select host, user, password from mysql.user;
 +
 
 +
 
 +
You can create a new user account with the command:
  
 
   CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
 
   CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
  
Anna oikeudet uudelle käyttäjätilillä. Seuraava komento antaa lähes kaikki oikeudet paitsi muokata käyttäjätilejä.
+
Give privileges for the new user account. The following command gives pretty much all privileges except the ones for modifying user accounts.
  
 
   GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
 
   GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
  
Tämä komento antaa täydelliset oikeudet käyttäjätileille ja tietokantonnoille.
+
This command gives the user all privileges including databases and users accounts.
  
 
   GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'%' WITH GRANT OPTION;
 
   GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'%' WITH GRANT OPTION;
  
Vahvista oikeudet:
+
Confirm privileges:
  
 
   FLUSH PRIVILEGES;
 
   FLUSH PRIVILEGES;
  
Lisää käyttäjätilille tiettyjä oikeuksia:
+
Add certain privileges to a user account:
  
 
  GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;
 
  GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;
  
Poista käyttäjätililtä oikeuksia:
+
Remove certain privileges from a user account:
  
 
   REVOKE [type of permission] ON [database name].[table name] FROM ‘[username]’@‘localhost’;
 
   REVOKE [type of permission] ON [database name].[table name] FROM ‘[username]’@‘localhost’;
  
Poista käyttäjätili
+
Remove user account:
  
 
   DROP USER ‘demo’@‘localhost’;
 
   DROP USER ‘demo’@‘localhost’;
  
== Master ja Slave ==
+
== Master and Slave ==
  
MySQL palvelimen asynkroninen synkronointi Masterista Slave palvelimiin.  
+
Asynchronous synchronization of a MySQL server from Master server to Slave servers.  
  
 
=== Master ===
 
=== Master ===
  
Tämä tulee Master palvelimen konfiguraatioon:
+
This goes in the Master server's configuration:
  
 
   nano /etc/mysql/my.cnf
 
   nano /etc/mysql/my.cnf
  
Lisää nämä mysqld loppuun
+
Add these to the end of mysqld
  
 
  [mysqld]
 
  [mysqld]
Rivi 91: Rivi 96:
 
  auto_increment_offset = 1
 
  auto_increment_offset = 1
  
Muodosta yhteys MySQL palvelimeen:
+
Form connection to MySQL server:
  
 
  mysql -u root -p
 
  mysql -u root -p
  
Luodaan käyttäjätili  replicant, kirjautumisoikeudella mistä tahansa % ja salasanalla password.
+
We create the user account replicant, with login rights from wherever % and password is just "password"
  
 
   create user replicant@'%' identified by 'password';
 
   create user replicant@'%' identified by 'password';
  
Määritetään käyttäjälle oikeudet:
+
Define privileges for the user:
  
 
   GRANT SELECT, PROCESS, FILE, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD ON *.* TO replicant@'%';
 
   GRANT SELECT, PROCESS, FILE, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD ON *.* TO replicant@'%';
Rivi 106: Rivi 111:
 
=== Slave ===
 
=== Slave ===
  
Tämä tulee Slave palvelimen konfiguraatioon:
+
This goes in the Slave server's configuration:
  
Muodosta yhteys tietokantapalvelimeen ja anna Master palvelimen IP-osoite, käyttäjätunnus ja salasana.
+
Establish a connection to the database server and give the Master server's IP-address, username and password.
  
 
  HANGE MASTER TO
 
  HANGE MASTER TO
Rivi 115: Rivi 120:
 
  MASTER_PASSWORD='password';
 
  MASTER_PASSWORD='password';
  
Käynnistä Slave palvelin
+
Start the Slave server
  
 
  Start slave;
 
  Start slave;
  
Varmista että kaikki toimii:
+
Make sure that everything works:
  
 
  show slave status\G
 
  show slave status\G
  
  
Molempiin suuntiin synkroidessasi, sinun tulee määrittää molemmat palvelimet Slaveksi ja Masteriksi.
+
When synchronizing both ways, you must define the role for both servers, one Slave and one Master.
  
== MYSQL ja PHP5 ==
+
== MYSQL and PHP5 ==
  
Voit hakea ja lähettää MySQL tietokantaan tietoa PHP:n avulla. Tämä onnistuu yksinkertaisesti tälläisellä funktiolla:
+
You can fetch and send information to the MySQL database using PHP. This is simply done by the command:
  
 
   <?php
 
   <?php
Rivi 135: Rivi 140:
 
   ?>
 
   ?>
  
Muokkaa tätä esimerkiksi:  
+
Modify this to for example:  
  
 
   <?php
 
   <?php
   //Muodostetaan yheyttä tietokantaan
+
   //Establishing connection to database
 
   $con = mysqli_connect(127.0.0.1,root,Qwerty123,database);
 
   $con = mysqli_connect(127.0.0.1,root,Qwerty123,database);
   //Suljetaan yhteys tietokantaan
+
   //Closing connection to database
 
   mysqli_close($con);
 
   mysqli_close($con);
 
   ?>
 
   ?>
  
Eli palvelimen osoite, käyttäjätunnus, salasana ja tietokanta joka valitaan.
+
So basically we select the server address, username, password and the database.
  
  
MySQL tietokannasta tulostaminen
+
Printing from MySQL database:
  
 
   <?php
 
   <?php
 
   $con=mysqli_connect("127.0.0.1","root","Qwerty123","database");
 
   $con=mysqli_connect("127.0.0.1","root","Qwerty123","database");
 
   if (mysqli_connect_errno()) {
 
   if (mysqli_connect_errno()) {
   echo "Yhdistäminen epäonnistui " . mysqli_connect_error();
+
   echo "Connecting failed. " . mysqli_connect_error();
 
   }
 
   }
 
   $result = mysqli_query($con,"SELECT * FROM data");
 
   $result = mysqli_query($con,"SELECT * FROM data");
Rivi 161: Rivi 166:
 
   ?>
 
   ?>
  
Huomaa, että tietokantaan täytyy kirjoittaa jotakin että voit tulostaa tietoa sieltä.
+
Note that the database needs to have something written in it in order for you to print from it.
  
Talllenna edelliset konfiguroinnit mysql.php ja laita sitten esim. index.php tiedostoon:
+
Save the previous configurations into mysql.php and then for instance write the following in the index.php file:
  
 
   <?php
 
   <?php
Rivi 169: Rivi 174:
 
   ?>
 
   ?>
  
Näin sinun täytyy laittaa kaikkiin haluamaasi php tiedostoon, jotka käyttää tietokantaa.
+
You have to write this in every php file that will use the database.
  
== Varmuuskopio ==
+
== Backup ==
  
 
[[Automysqlbackup]]
 
[[Automysqlbackup]]
  
== Lähteet ==
+
== Sources ==
  
 
https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql
 
https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql

Nykyinen versio 23. marraskuuta 2015 kello 21.10

Muut kielet:
English • ‎suomi

Mysql is a handy database which is easy to manage using the phpmyadmin application. Using phpmyadmin requires HTTP server software as well for instance Apache2 or Nginx and Php5.

Installation

 aptitude install mysql-server

Give the mysql root user login password in installation. Do not forget the password. You can create more user accounts and change passwords later.

You can also use a mysql database without phpmyadmin but it helps a lot with managing it.

aptitude install mysql-client

MySQL configuration

Configuration file can be found in Ubuntu and Debian under:

 /etc/mysql/my.cnf

The port the MySQL server listens to. By default it is set to 3306.

[client]
port 3306

Address the server will listen to. Put a # number sign in front of the line and it will listen to all IP addresses.

bind-address               = 127.0.0.1

Command Line

Mysql databases can also be managed by command line.

You can connect to a mysql server with command line by:

 mysql -u root -p

Give the mysql root user password.

User Accounts

List user accounts:

  select host, user, password from mysql.user;


You can create a new user account with the command:

 CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Give privileges for the new user account. The following command gives pretty much all privileges except the ones for modifying user accounts.

 GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

This command gives the user all privileges including databases and users accounts.

 GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'%' WITH GRANT OPTION;

Confirm privileges:

 FLUSH PRIVILEGES;

Add certain privileges to a user account:

GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;

Remove certain privileges from a user account:

 REVOKE [type of permission] ON [database name].[table name] FROM ‘[username]’@‘localhost’;

Remove user account:

  DROP USER ‘demo’@‘localhost’;

Master and Slave

Asynchronous synchronization of a MySQL server from Master server to Slave servers.

Master

This goes in the Master server's configuration:

 nano /etc/mysql/my.cnf

Add these to the end of mysqld

[mysqld]
...
log-bin=mysql-bin
server-id	= 1
auto_increment_increment = 10
auto_increment_offset = 1

Form connection to MySQL server:

mysql -u root -p

We create the user account replicant, with login rights from wherever % and password is just "password"

 create user replicant@'%' identified by 'password';

Define privileges for the user:

 GRANT SELECT, PROCESS, FILE, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD ON *.* TO replicant@'%';
 Flush Privileges; 

Slave

This goes in the Slave server's configuration:

Establish a connection to the database server and give the Master server's IP-address, username and password.

HANGE MASTER TO
MASTER_HOST='10.10.10.100',
MASTER_USER='replicant',
MASTER_PASSWORD='password';

Start the Slave server

Start slave;

Make sure that everything works:

show slave status\G


When synchronizing both ways, you must define the role for both servers, one Slave and one Master.

MYSQL and PHP5

You can fetch and send information to the MySQL database using PHP. This is simply done by the command:

 <?php
 mysqli_connect(host,username,password,dbname);
 mysqli_close($con);
 ?>

Modify this to for example:

 <?php
 //Establishing connection to database
 $con = mysqli_connect(127.0.0.1,root,Qwerty123,database);
 //Closing connection to database
 mysqli_close($con);
 ?>

So basically we select the server address, username, password and the database.


Printing from MySQL database:

 <?php
 $con=mysqli_connect("127.0.0.1","root","Qwerty123","database");
 if (mysqli_connect_errno()) {
 echo "Connecting failed. " . mysqli_connect_error();
 }
 $result = mysqli_query($con,"SELECT * FROM data");
 while($row = mysqli_fetch_array($result)) {
 echo $row['mysql'];
 }
 mysqli_close($con);
 ?>

Note that the database needs to have something written in it in order for you to print from it.

Save the previous configurations into mysql.php and then for instance write the following in the index.php file:

 <?php
 include_once ('mysql.php');
 ?>

You have to write this in every php file that will use the database.

Backup

Automysqlbackup

Sources

https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql

Mainos / Advertisement: