My SQL Development

Installing and administering a mysql server to hand it here

Installing the mano:

Installing more init mysql server root password:

Download:

http://dev.mysql.com/downloads/mysql/5.0.html

Linux (non RPM packages) downloads

Unpack it in / opt /

adduser mysql
cd / opt
cd mysql-version
chown-R mysql.
chgrp-R mysql.
scripts / mysql_install_db - user = mysql
chown-R root.
chown-R mysql data
bin / mysqld_safe - user = mysql &
cd / opt
ln-s mysql mysql-version
Set a root Password:

mysqladmin-u root password "password"

or after if you want to change:

use mysql;

select * from user;

UPDATE mysql.user SET Password = PASSWORD ( 'password') WHERE User = 'root';

FLUSH PRIVILEGES;

Envirronement:

Important!

Place:

pathmunge / opt / mysql / bin
in / etc / profile (su - to take into account right away) and in / etc / ld.so.conf

/ opt / mysql / lib
Remember to take into account your system the changes you just made:

ldconfig
To load libraries and:

source / etc / profile
For mysql binary in the PATH.

init.d

/ bin / bash
PATH = / sbin: / bin: / usr / bin: / usr / sbin: / opt / mysql / bin
case "$ 1" in
    start)
        cd / opt / mysql
        . / bin / mysqld_safe - user = mysql &
        ;;
    stop)
        cd / opt / mysql
        killall mysqld &
        ;;
    *)
        $ echo "Usage: $ 0 (start | stop)"
        RETVAL = 1
esac
exit
If you have a more complete here

To configure and secure your mysql type:

bin / mysql_secure_installation

Creating a database and user

Database creation:

CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
creating the user:

GRANT ALL PRIVILEGES ON dbname .* TO 'user' @ 'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
reload rights:

FLUSH PRIVILEGES;
Save all your databases in one file. Sql:

 mysqldump-u root-ppassword - default-character-set = latin1-f - all-databases | bzip2> / data / mysql-dump / mysql-dump-all-base.sql.gz
Latin1 option to have no worries focus if re-import, bzip2 file that takes up less space, biensur these two options are optional (bzip2-d to decompress), by the cons-f important in my opinion (if mysqldump encounters a mistake keep going.

Save a database to a file. Sql:

mysqldump-u root-h localhost-p - opt-name basis> name-base.sql
Save a table in a database:

mysqldump-u login-h localhost-p - opt name-based name-table> table-name-of-nombase.sql
Inject. Sql:

-D database which must already exist.

mysql-u root-p-D test <test.sql
Important information from the database server:

To use, simply connect to the server as root and run the following command:

mysql> \ s
--------------
    mysql Ver 14.12 Distrib 5.0.51a, for redhat-linux-gnu (i386) using readline 5.0

    Connection id: 2
    Current database:
    Current user: root @ localhost
    SSL: Not in use
    Current pager: stdout
    Using outfile:''
    Using delimiter:;
    Server version: 5.0.51a Source distribution
    Protocol version: 10
    Connection: Localhost via UNIX socket
    Server characterset: utf8
    Db characterset: utf8
    Client characterset: latin1
    Conn. characterset: latin1
    UNIX socket: / var / lib / mysql / mysql.sock
    Uptime: 12 sec

    Threads: 1 Questions: 5 Slow queries: 0 Opens: 12 Flush tables: 1 Open tables: 6 Queries per second avg: 0.417
    --------------
The order allows us to quickly see the uptime of the server, its version, its encoding, the number of slow queries, the number of open tables ... etc. ...

Problem on your data: tables and bases

I use it control me from time to time, you can also put in a cron job:

. / mysqlcheck-u root-ppassword - all-databases - auto-repair
Display the connections to the database in real time

mysql-e 'SHOW PROCESSLIST'-ppassword
Modified fields and values from the command line:

show databases; (see all databases)
use foo; (use the basic plop)
show tables; (see tables plop)
select * from user; (see the contents of the user table)
update user set Host = 'WWW-SRV' where .43.120.2 Host = '191 '; (change the field value of 191.43.120.2 to host SRV-WWW)
insert into user values ( 'SRV-WWW2', 'root', '* C4890413824', 'Y', 'Y', 'Y',,, 0,0) (add a field with values in brackets)
Adding a configuration file my.cnf

With init script provides a bit more than that used in this article you rez the possibility of setting up a configuration file my.cnf for your mysql server.

For init script you find in this item.

Now you only have to create a file my.cnf in / etc / with content like:

# # my.cnf
[mysqld]
datadir = / opt / mysql / data
socket = / tmp / mysql.sock
user = mysql
# Default to using old password format for compatibility with mysql 3.x
# Clients (those using the mysqlclient10 compatibility package).
old_passwords = 1
[mysqld_safe]
log-error = / var / log / mysqld.log
pid-file = / opt / mysql / data / hostname.pid
Get the size of all databases in MB:

SELECT TABLE_SCHEMA, round (sum (data_length index_length +) / 1024/1024, 4) AS 'Size (MB) "
FROM INFORMATION_SCHEMA.TABLES
GROUP BY TABLE_SCHEMA;
Get the size of a table in a given base:

SELECT TABLE_SCHEMA, round (sum (data_length index_length +) / 1024/1024, 4) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dotclear' AND table_name = 'user';

Here you have the size of the table user in the database dotclear