Uses of mysqladmin with a remote server

This is how you can use the mysqladmin tool with a remote MySQL server.

Assume MySQL is installed on 172.22.128.25, and user remote with password 12345 on your current host has been granted the necessary rights.

Ping

mysqladmin -h 172.22.128.25 ping --user=remote --password=12345

Get status

mysqladmin -h 172.22.128.25 status --user=remote --password=12345

Get version

mysqladmin -h 172.22.128.25 version --user=remote --password=12345

Create a database

mysqladmin -h 172.22.128.25 create logdb --user=remote --password=12345

Drop a database

mysqladmin -h 172.22.128.25 drop logdb --user=remote --password=12345

MySQL commands for a developer

This is a reference of some common MySQL commands useful to a developer.

Start the server

MySQL should be installed as a service. However, if it’s set to manual you may start it with:

mysqld-nt --console

Stop the server

mysqladmin -u root -p shutdown

Login

Just use the entry in the Start menu.

Start → Programs → MySQL → MySQL server 5.0 → MySQL Command Line Client

Even better, if the bin folder of the MySQL installation is in the path you may simply type

mysql -u root -p

List database schemas

show databases;

Add a schema

CREATE DATABASE fcom;

Delete a schema

DROP DATABASE fcom;

Select the current schema

USE fcom;

List available tables

show tables;

Add a table

INSERT TABLE clients (c_id INT AUTO_INCREMENT, c_name VARCHAR(50),
    c_address VARCHAR(200), c_tel VARCHAR(20), PRIMARY KEY(c_id));

Delete a table

DROP TABLE clients;

Add an account

GRANT ALL PRIVILEGES ON fcom.* TO 'george'@'localhost'
IDENTIFIED BY 'mypass' WITH GRANT OPTION;

Where fcom is the database schema this user may access, george is the username and mypass is the password. Note that the username is explicitly associated with a host. This command grants privileges to george, only when he logins from the same computer as the MySQL instance.

Show privileges of an account

SHOW GRANTS FOR 'george'@'localhost';

Delete an account

DROP USER 'george'@'localhost';

References

• MySQL in 3 minutes
• JDBC in 10 minutes

Copy a database to another MySQL instance

We want to copy a whole database along with its tables and data to another MySQL instance.

The database is called bank_development.

In the source machine

mysqldump -u root -p bank_development > ayo.sql

Take the file ayo.sql to the target machine.

In the target machine

Start the MySQL client from the same folder with ayo.sql.

mysql -u root -p

Create the database schema,

CREATE DATABASE bank_development;

use it,

use bank_development;

and make the actual migration.

source ./ayo.sql

That’s it!

Review

This is a comfortable way for a developer to move/copy/backup his database.

MySQL σε 3 λεπτά

Αυτό το άρθρο είναι ένας γρήγορος οδηγός MySQL.

Βήμα 1. Κατέβασε την MySQL

Κατέβασε την MySQL από το mysql.com → downloads → MySQL Community Server → Windows ZIP/Setup.EXE (x86) → Pick a mirror → No thanks, just take me to the downloads → Greece [National Technical University of Athens] HTTP → mysql-5.0.51b-win32.zip

Βήμα 2. Εγκατέστησε την MySQL

Η ΜySQL εγκαθίσταται πολύ εύκολα. Απλά ακολούθησε της οδηγίες βήμα προς βήμα.

Αφού ολοκληρωθεί η εγκατάσταση, ανοίγει ένας οδηγός ρυθμίσεων: Στο παράθυρο με τις γλώσσες διάλεξε “Best Support For Multilingualism”. Επίσης καλό είναι σημειώσεις τον κωδικό του διαχειριστή (root password).

Βήμα 3. Ξεκίνησε την MySQL

Για να ξεκινήσεις την MySQL πήγαινε Start → Programs → MySQL → MySQL Server 5.0 → MySQL Command Line Client → και δώσε τον κωδικό του διαχειριστή.

Για να ξεκινήσεις τον server σε παλαιότερη έκδοση των Windows (π.χ. Millenium) πήγαινε Start → Run και γράψε "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld" --console. Έπειτα Start → Programs → MySQL → MySQL Server 5.0 → MySQL Command Line Client → και δώσε τον κωδικό του διαχειριστή.

Βήμα 4. Φτιάξε το σχήμα

Για να φτιάξεις μια βάση απλά γράψε create database hello;

Για επιβεβαίωση δώσε show databases; κι έπειτα use hello;

Βήμα 5. Φτιάξε έναν πίνακα

Αυτή η απλή βάση έχει να κάνει με άτομα. Για κάθε άτομο μας ενδιαφέρει 1. το όνομα, 2. η ηλικία και 3. ένας μοναδικός αριθμός (identifier = αναγνωριστικό = κλειδί).

Για να φτιάξεις έναν πίνακα γράψε CREATE TABLE persons (name VARCHAR(255), age SMALLINT, id BIGINT AUTO_INCREMENT, primary key (id));

Για επιβεβαίωση show tables; κι έπειτα describe persons;

Βήμα 6. Βάλε δεδομένα

Για να βάλεις δεδομένα στον πίνακα απλά γράψε INSERT INTO persons (name, age) VALUES ('Michael Jordan', 45);

Ας βάλουμε την Jennifer Lopez: INSERT INTO persons (name, age) VALUES ('JLo', 39);

Και βέβαια τον Al Pacino: INSERT INTO persons (name, age) VALUES ('Al Pacino', 68);

Βήμα 7. Πάρε δεδομένα

Για πάρεις όλα τα άτομα: SELECT * FROM persons;

Για να πάρεις όλα τα άτομα άνω των 50 ετών: SELECT * FROM persons WHERE age > 50;

Για να πάρεις όλους τους “Μιχάληδες”: SELECT * FROM persons WHERE name LIKE '%Michael%';

Βήμα 8. Άλλαξε μια εγγραφή

Η Jennifer Lopez ζήτησε προσωπικά να αλλάξουμε την ηλικία της. Πρώτα μαθαίνουμε το id SELECT id FROM persons WHERE name='JLo';
και μετά αλλάζουμε της ηλικία ως εξής UPDATE persons SET age=28 WHERE id=2;

Για να δούμε ότι όλα πήγαν καλά SELECT * FROM persons

Βήμα 9. Βγες από την MySQL

Για να βγεις απλά γράψε exit

Για να κλείσεις τον server σε παλαιότερη έκδοση των Windows (π.χ. ΜΕ) πήγαινε Start → Run
και γράψε "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqladmin" shutdown

Όλες οι εξελίξεις για την MySQL βρίσκονται στο mysql.com, πλήρεις οδηγίες στο Start → Programs → MySQL → MySQL Server 5.0 → MySQL Manual – Table Of Contents. Τέλος, ένα ωραίο μάθημα SQL είναι το w3schools.com.

Ευχαριστώ.

MySQL in 3 minutes

This is a quick-start MySQL tutorial. You’ll create a simple database and do some useful read / write operations.

Step 1. Download MySQL

Download MySQL from mysql.com → downloads → Take me to the community downloads! → Scroll down to Windows downloads → Windows Essentials (x86) → Pick a mirror → No thanks, just take me to the downloads → Greece [National Technical University of Athens] HTTP → mysql-essential-5.1.31-win32.msi

Step 2. Install MySQL

Installing MySQL is very easy. Just follow the steps one-by-one. After installation completes, make sure to select the option “Configure the MySQL Server now” and click “Finish”.

In the configuration wizard keep pressing next until you the dialog about character encoding. Here, you should choose “Best Support For Multilingualism”.

Press next; the last important setting is about security. Provide a root password and write it on a paper.

Step 3. Start MySQL

In order to start using MySQL go to Start → Programs → MySQL → MySQL Server 5.1 → MySQL Command Line Client → and give the root password from the previous step.

To start MySQL in an older version of Windows (e.g. ME) go to Start → Run and write "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld" --console. Then go to Start → Programs → MySQL → MySQL Server 5.1 → MySQL Command Line Client → and give the root password.

Step 4. Create a database

To create a database in MySQL just type:

create database hello;

For confirmation state

show databases;

and then:

use hello;

Step 5. Create a table

This simple database is about persons. For every person we are interested about 1. the name, 2. the age and 3. a unique number (identifier).

To create a table in MySQL write

CREATE TABLE persons (name VARCHAR(255), age SMALLINT,
             id BIGINT AUTO_INCREMENT, primary key (id));

To confirm write,

show tables;

Displaying the tables of a database

and then:

describe persons;

Step 6. Store information (insert data)

To insert some data to your table simply say:

INSERT INTO persons (name, age) VALUES ('Michael Jordan', 45);

Let’s store Jennifer Lopez also:

INSERT INTO persons (name, age) VALUES ('JLo', 39);

How about Al Pacino?

INSERT INTO persons (name, age) VALUES ('Al Pacino', 68);

Step 7. Get information (select data)

To get all persons:

SELECT * FROM persons;

Selecting the data of a table

To get all persons that are older than 50 years:

SELECT * FROM persons WHERE age > 50;

To get all ‘Michaels’:

SELECT * FROM persons WHERE name LIKE '%Michael%';

Step 8. Change some info (update data)

We have the business requirement to change the age of Jennifer Lopez! To accomplish this task, we may use her id,

UPDATE persons SET age = 29 WHERE id = 2;

or directly her name:

UPDATE persons SET age = 29 WHERE name = 'JLo';

To confirm that her age is now proper:

SELECT * FROM persons;

Updating a value in the table

Step 9. Exit MySQL

To exit MySQL simply type

exit

To shut down MySQL in older versions of Windows (e.g. ME) go to Start → Run
and write "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqladmin" shutdown

All the action about MySQL is at mysql.com. Finally, you may get more info about SQL at w3schools.com.