Sunday, April 17, 2005
Problem in Mysql with equality comparison with float
There is a general problem when testing a floating point field for equality. The test (field=14.40) doesn't work because field is not exactly 14.40, but it could be 13.9999 or 14.0001. Use this test instead: (field between 13.9999 and 14.0001). Moreover, as a general rule, if you want precise results, don't use float, but decimal, and test by ranges (between y and z), instead of exact values (= x).
Do a join on the same table in MySQL
You can join the same table in mysql!
mysql> select mv2.name, mv1.quantity
-> from dw.MAILVRS mv1, dw.MAILVRS mv2
-> where mv1.version_recnum = 124475 and mv1.package_recnum = mv2.dataflex_recnum_one;
+---------+----------+
| name | quantity |
+---------+----------+
| Kit A | 25000 |
| Control | 50000 |
| Kit E | 25000 |
+---------+----------+
mysql> select mv2.name, mv1.quantity
-> from dw.MAILVRS mv1, dw.MAILVRS mv2
-> where mv1.version_recnum = 124475 and mv1.package_recnum = mv2.dataflex_recnum_one;
+---------+----------+
| name | quantity |
+---------+----------+
| Kit A | 25000 |
| Control | 50000 |
| Kit E | 25000 |
+---------+----------+
MySQL outer join SQL tutorial/examples
First of all, outer joins are for instances where info is being looked up on another table and you want rows returned even though nothing was matched for a particular id, e.g.:
create table food (
id int unsigned primary key auto_increment,
food char(16),
yummy char(1),
animal char(16)
);
create table animals (
name char(16) primary key,
weight int unsigned
);
insert into food values
(NULL, 'apple', 'Y', 'human'),
(NULL, 'oranges', 'Y', 'human'),
(NULL, 'apple', 'N', 'dog'),
(NULL, 'oranges', 'N', 'dog'),
(NULL, 'apple', 'N', 'cat'),
(NULL, 'oranges', 'N', 'cat');
insert into animals values
('cat', 12),
('dog', 25);
table 'food'
id food yummy animal
--- ---- ----- ------
1 apple Y human
2 oranges Y human
3 apple N dog
4 oranges N dog
5 apple N cat
6 oranges N cat
table 'animals'
name weight
---- ------
Cat 12
Dog 25
Let's say you want a list of all the foods and the weight of the animals.
If you do:
select f.*, a.weight
from food f, animals a
where f.animal = a.name
In this case the result would be:
+----+---------+-------+--------+--------+
| id | food | yummy | animal | weight |
+----+---------+-------+--------+--------+
| 5 | apple | N | cat | 12 |
| 6 | oranges | N | cat | 12 |
| 3 | apple | N | dog | 25 |
| 4 | oranges | N | dog | 25 |
+----+---------+-------+--------+--------+
Notice that humans is missing, but if you want the humans to show up even
though there's no human entry in the 'animals' table you have to do an
outer join (aka LEFT [OUTER] JOIN in Mysql):
select f.*, a.weight
from food f
LEFT JOIN animals a on (f.animal=a.name)
In this case the result would be:
+----+---------+-------+--------+--------+
| id | food | yummy | animal | weight |
+----+---------+-------+--------+--------+
| 1 | apple | Y | human | NULL |
| 2 | oranges | Y | human | NULL |
| 3 | apple | N | dog | 25 |
| 4 | oranges | N | dog | 25 |
| 5 | apple | N | cat | 12 |
| 6 | oranges | N | cat | 12 |
+----+---------+-------+--------+--------+
Here are some more examples:
SELECT T.name, V.value, T.unit, T.id, A.Product_id
FROM attribute_type AS T
LEFT JOIN attribute AS A ON ( T.id = A.type_id AND A.Product_id = 21 )
LEFT JOIN attribute_value AS V ON ( A.value_id = V.id );
select j.id, j.job_id, d.NAMES, e.DETAILS1, j.file_name, j.job_owner, j.import_date
from db.summary_jobs j
left join db.EST e on (j.job_id=e.JOB_NUMBER)
left join db.DEB d on (e.DEBTOR=d.AC_NO)
order by j.job_id desc limit
create table food (
id int unsigned primary key auto_increment,
food char(16),
yummy char(1),
animal char(16)
);
create table animals (
name char(16) primary key,
weight int unsigned
);
insert into food values
(NULL, 'apple', 'Y', 'human'),
(NULL, 'oranges', 'Y', 'human'),
(NULL, 'apple', 'N', 'dog'),
(NULL, 'oranges', 'N', 'dog'),
(NULL, 'apple', 'N', 'cat'),
(NULL, 'oranges', 'N', 'cat');
insert into animals values
('cat', 12),
('dog', 25);
table 'food'
id food yummy animal
--- ---- ----- ------
1 apple Y human
2 oranges Y human
3 apple N dog
4 oranges N dog
5 apple N cat
6 oranges N cat
table 'animals'
name weight
---- ------
Cat 12
Dog 25
Let's say you want a list of all the foods and the weight of the animals.
If you do:
select f.*, a.weight
from food f, animals a
where f.animal = a.name
In this case the result would be:
+----+---------+-------+--------+--------+
| id | food | yummy | animal | weight |
+----+---------+-------+--------+--------+
| 5 | apple | N | cat | 12 |
| 6 | oranges | N | cat | 12 |
| 3 | apple | N | dog | 25 |
| 4 | oranges | N | dog | 25 |
+----+---------+-------+--------+--------+
Notice that humans is missing, but if you want the humans to show up even
though there's no human entry in the 'animals' table you have to do an
outer join (aka LEFT [OUTER] JOIN in Mysql):
select f.*, a.weight
from food f
LEFT JOIN animals a on (f.animal=a.name)
In this case the result would be:
+----+---------+-------+--------+--------+
| id | food | yummy | animal | weight |
+----+---------+-------+--------+--------+
| 1 | apple | Y | human | NULL |
| 2 | oranges | Y | human | NULL |
| 3 | apple | N | dog | 25 |
| 4 | oranges | N | dog | 25 |
| 5 | apple | N | cat | 12 |
| 6 | oranges | N | cat | 12 |
+----+---------+-------+--------+--------+
Here are some more examples:
SELECT T.name, V.value, T.unit, T.id, A.Product_id
FROM attribute_type AS T
LEFT JOIN attribute AS A ON ( T.id = A.type_id AND A.Product_id = 21 )
LEFT JOIN attribute_value AS V ON ( A.value_id = V.id );
select j.id, j.job_id, d.NAMES, e.DETAILS1, j.file_name, j.job_owner, j.import_date
from db.summary_jobs j
left join db.EST e on (j.job_id=e.JOB_NUMBER)
left join db.DEB d on (e.DEBTOR=d.AC_NO)
order by j.job_id desc limit
How to alter mysql table column to AUTO_INCREMENT
mysql> set insert_id=7;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table fixtures modify column id int auto_increment;
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings
Query OK, 0 rows affected (0.00 sec)
mysql> alter table fixtures modify column id int auto_increment;
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings
MySQL INSERT SELECT example
insert into user (id, name, email, passwd, ref, joindate) select idm, user_name, email, pass, refferal, sysdate() from db.members;
Export from mysql into fixed length file
SELECT * INTO OUTFILE file_name
FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY "\n"
FROM my_table;
FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY "\n"
FROM my_table;
Mysql Search and Replace example
Doing search and replace in mysql, e.g.:
update tablename set field = replace(field,'search_for_this','replace_with_this');
update tablename set field = replace(field,'search_for_this','replace_with_this');
MySQL slow queries log
start safe_mysqld like this to catch slow queries:
./bin/safe_mysqld --log-slow-queries &
OR
To use it, you need to add --log-slow-queries to your mysqld startup.
I added it to my /etc/rc.d/init.d/mysql file like so:
$bindir/safe_mysqld --user=$mysql_daemon_user --datadir=$datadir --pid-file=$pid_file --log=$log_file --log-slow-queries &
It does appear to put stuff into a different place, namely:
/var/lib/mysql/hostname-slow.log
./bin/safe_mysqld --log-slow-queries &
OR
To use it, you need to add --log-slow-queries to your mysqld startup.
I added it to my /etc/rc.d/init.d/mysql file like so:
$bindir/safe_mysqld --user=$mysql_daemon_user --datadir=$datadir --pid-file=$pid_file --log=$log_file --log-slow-queries &
It does appear to put stuff into a different place, namely:
/var/lib/mysql/hostname-slow.log
MySQL replication instructions
As of version 3.23.15 (try to use 3.23.29 or later), MySQL supports one-way replication. Since most web applications usually have more reads than writes, an architecture which distributes reads across multiple servers can be very beneficial.
In typical MySQL fashion, setting up replication is trivial. On your master server add this to your my.cnf file:
[mysqld]
log-bin
server-id=1
And add a replication user id for slaves to log in as:
GRANT FILE ON *.* TO repl@"%" IDENTIFIED BY 'foobar';
Then on your slave servers:
[mysqld]
set-variable = max_connections=200
log-bin
master-host=192.168.0.1
master-user=repl
master-password=foobar
master-port=3306
server-id=2
Make sure each slave has its own unique server-id. And since these will be read-only slaves, you can start them with these options to speed them up a bit:
--skip-bdb --low-priority-updates --delay-key-write-for-all-tables
Stop your master server. Copy the table files to each of your slave servers. Restart the master, then start all the slaves. And you are done. Combining MySQL replication with a Squid reverse cache and redirector and you might have an architecture like this:
You would then write your application to send all database writes to the master server and all reads to the local slave. It is also possible to set up two-way replication, but you would need to supply your own application-level logic to maintain atomicity of distributed writes. And you lose a lot of the advantages of this architecture if you do this as the writes would have to go to all the slaves anyway.
In typical MySQL fashion, setting up replication is trivial. On your master server add this to your my.cnf file:
[mysqld]
log-bin
server-id=1
And add a replication user id for slaves to log in as:
GRANT FILE ON *.* TO repl@"%" IDENTIFIED BY 'foobar';
Then on your slave servers:
[mysqld]
set-variable = max_connections=200
log-bin
master-host=192.168.0.1
master-user=repl
master-password=foobar
master-port=3306
server-id=2
Make sure each slave has its own unique server-id. And since these will be read-only slaves, you can start them with these options to speed them up a bit:
--skip-bdb --low-priority-updates --delay-key-write-for-all-tables
Stop your master server. Copy the table files to each of your slave servers. Restart the master, then start all the slaves. And you are done. Combining MySQL replication with a Squid reverse cache and redirector and you might have an architecture like this:
You would then write your application to send all database writes to the master server and all reads to the local slave. It is also possible to set up two-way replication, but you would need to supply your own application-level logic to maintain atomicity of distributed writes. And you lose a lot of the advantages of this architecture if you do this as the writes would have to go to all the slaves anyway.
MySQL SELECT CASE example
select
CASE month when "01" then "January"
when "02" then "February"
when "03" then "March"
when "04" then "April"
when "05" then "May"
when "06" then "June"
when "07" then "July"
when "08" then "August"
when "09" then "September"
when "10" then "October"
when "11" then "November"
when "12" then "December"
END
from calendar where year = "2005" order by month
CASE month when "01" then "January"
when "02" then "February"
when "03" then "March"
when "04" then "April"
when "05" then "May"
when "06" then "June"
when "07" then "July"
when "08" then "August"
when "09" then "September"
when "10" then "October"
when "11" then "November"
when "12" then "December"
END
from calendar where year = "2005" order by month
Transactions example
Transactions are used to make ensure that a series of statements either all get processed or don't get processed at all -- basically all or nothing. Why is this important? Let's consider a simple banking example. You transfer $500 from one bank account into another:
1. - $500 from account A
2. + $500 to account B
Step 1 processes successfully, but something bad happens between step 1 and 2. Where is your money now -- you just lost $500!! This is clearly not acceptable, we need to be able to confidently say that steps 1 and 2 must all process successfully, or none of it can process at all.
Enter the concept of transactions. With transactions, we would be able to do this:
1. BEGIN TRANSACTION
2. - $500 from account A
3. + $500 to account B
4. END TRANSACTION
Now steps 1 to 4 are treated as one atomic operation. If anything fails between steps 1 to 4, the entire operation is aborted and we revert back to the way things were before we started. The worst that can happen here is that the money doesn't get transferred, but at least you will not lose $500.
1. - $500 from account A
2. + $500 to account B
Step 1 processes successfully, but something bad happens between step 1 and 2. Where is your money now -- you just lost $500!! This is clearly not acceptable, we need to be able to confidently say that steps 1 and 2 must all process successfully, or none of it can process at all.
Enter the concept of transactions. With transactions, we would be able to do this:
1. BEGIN TRANSACTION
2. - $500 from account A
3. + $500 to account B
4. END TRANSACTION
Now steps 1 to 4 are treated as one atomic operation. If anything fails between steps 1 to 4, the entire operation is aborted and we revert back to the way things were before we started. The worst that can happen here is that the money doesn't get transferred, but at least you will not lose $500.
Setup New Users in MySQL
The examples below show how to use the mysql client to set up new users. These examples assume that privileges are set up according to the defaults described in the previous section. This means that to make changes, you must be on the same machine where mysqld is running, you must connect as the MySQL root user, and the root user must have the insert privilege for the mysql database and the reload administrative privilege. Also, if you have changed the root user password, you must specify it for the mysql commands below.
You can add new users by issuing GRANT statements:
shell> mysql --user=root mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost
IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@"%"
IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost;
mysql> GRANT USAGE ON *.* TO dummy@localhost;
These GRANT statements set up three new users:
monty
A full superuser who can connect to the server from anywhere, but who must use a password 'some_pass' to do so. Note that we must issue GRANT statements for both monty@localhost and monty@"%". If we don't add the entry with localhost, the anonymous user entry for localhost that is created by mysql_install_db will take precedence when we connect from the local host, because it has a more specific Host field value and thus comes earlier in the user table sort order.
admin
A user who can connect from localhost without a password and who is granted the reload and process administrative privileges. This allows the user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-* commands, as well as mysqladmin processlist . No database-related privileges are granted. (They can be granted later by issuing additional GRANT statements.)
dummy
A user who can connect without a password, but only from the local host. The global privileges are all set to 'N' -- the USAGE privilege type allows you to create a user with no privileges. It is assumed that you will grant database-specific privileges later.
You can also add the same user access information directly by issuing INSERT statements and then telling the server to reload the grant tables:
shell> mysql --user=root mysql
mysql> INSERT INTO user VALUES('localhost','monty',PASSWORD('some_pass'),
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user VALUES('%','monty',PASSWORD('some_pass'),
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user SET Host='localhost',User='admin',
Reload_priv='Y', Process_priv='Y';
mysql> INSERT INTO user (Host,User,Password)
VALUES('localhost','dummy','');
mysql> FLUSH PRIVILEGES;
Depending on your MySQL version, you may have to use a different number of 'Y' values above (versions prior to Version 3.22.11 had fewer privilege columns). For the admin user, the more readable extended INSERT syntax that is available starting with Version 3.22.11 is used.
Note that to set up a superuser, you need only create a user table entry with the privilege fields set to 'Y'. No db or host table entries are necessary.
The privilege columns in the user table were not set explicitly in the last INSERT statement (for the dummy user), so those columns are assigned the default value of 'N'. This is the same thing that GRANT USAGE does.
The following example adds a user custom who can connect from hosts localhost, server.domain, and whitehouse.gov. He wants to access the bankaccount database only from localhost, the expenses database only from whitehouse.gov, and the customer database from all three hosts. He wants to use the password stupid from all three hosts.
To set up this user's privileges using GRANT statements, run these commands:
shell> mysql --user=root mysql
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON bankaccount.*
TO custom@localhost
IDENTIFIED BY 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON expenses.*
TO custom@whitehouse.gov
IDENTIFIED BY 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON customer.*
TO custom@'%'
IDENTIFIED BY 'stupid';
The reason that we do to grant statements for the user 'custom' is that we want the give the user access to MySQL both from the local machine with Unix sockets and from the remote machine 'whitehouse.gov' over TCP/IP.
To set up the user's privileges by modifying the grant tables directly, run these commands (note the FLUSH PRIVILEGES at the end):
shell> mysql --user=root mysql
mysql> INSERT INTO user (Host,User,Password)
VALUES('localhost','custom',PASSWORD('stupid'));
mysql> INSERT INTO user (Host,User,Password)
VALUES('server.domain','custom',PASSWORD('stupid'));
mysql> INSERT INTO user (Host,User,Password)
VALUES('whitehouse.gov','custom',PASSWORD('stupid'));
mysql> INSERT INTO db
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv)
VALUES
('localhost','bankaccount','custom','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv)
VALUES
('whitehouse.gov','expenses','custom','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv)
VALUES('%','customer','custom','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;
The first three INSERT statements add user table entries that allow user custom to connect from the various hosts with the given password, but grant no permissions to him (all privileges are set to the default value of 'N'). The next three INSERT statements add db table entries that grant privileges to custom for the bankaccount, expenses, and customer databases, but only when accessed from the proper hosts. As usual, when the grant tables are modified directly, the server must be told to reload them (with FLUSH PRIVILEGES) so that the privilege changes take effect.
If you want to give a specific user access from any machine in a given domain, you can issue a GRANT statement like the following:
mysql> GRANT ...
ON *.*
TO myusername@"%.mydomainname.com"
IDENTIFIED BY 'mypassword';
To do the same thing by modifying the grant tables directly, do this:
mysql> INSERT INTO user VALUES ('%.mydomainname.com', 'myusername',
PASSWORD('mypassword'),...);
mysql> FLUSH PRIVILEGES;
You can also use xmysqladmin, mysql_webadmin, and even xmysql to insert, change, and update values in the grant tables. You can find these utilities in the Contrib directory of the MySQL Website.
You can add new users by issuing GRANT statements:
shell> mysql --user=root mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost
IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@"%"
IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost;
mysql> GRANT USAGE ON *.* TO dummy@localhost;
These GRANT statements set up three new users:
monty
A full superuser who can connect to the server from anywhere, but who must use a password 'some_pass' to do so. Note that we must issue GRANT statements for both monty@localhost and monty@"%". If we don't add the entry with localhost, the anonymous user entry for localhost that is created by mysql_install_db will take precedence when we connect from the local host, because it has a more specific Host field value and thus comes earlier in the user table sort order.
admin
A user who can connect from localhost without a password and who is granted the reload and process administrative privileges. This allows the user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-* commands, as well as mysqladmin processlist . No database-related privileges are granted. (They can be granted later by issuing additional GRANT statements.)
dummy
A user who can connect without a password, but only from the local host. The global privileges are all set to 'N' -- the USAGE privilege type allows you to create a user with no privileges. It is assumed that you will grant database-specific privileges later.
You can also add the same user access information directly by issuing INSERT statements and then telling the server to reload the grant tables:
shell> mysql --user=root mysql
mysql> INSERT INTO user VALUES('localhost','monty',PASSWORD('some_pass'),
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user VALUES('%','monty',PASSWORD('some_pass'),
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user SET Host='localhost',User='admin',
Reload_priv='Y', Process_priv='Y';
mysql> INSERT INTO user (Host,User,Password)
VALUES('localhost','dummy','');
mysql> FLUSH PRIVILEGES;
Depending on your MySQL version, you may have to use a different number of 'Y' values above (versions prior to Version 3.22.11 had fewer privilege columns). For the admin user, the more readable extended INSERT syntax that is available starting with Version 3.22.11 is used.
Note that to set up a superuser, you need only create a user table entry with the privilege fields set to 'Y'. No db or host table entries are necessary.
The privilege columns in the user table were not set explicitly in the last INSERT statement (for the dummy user), so those columns are assigned the default value of 'N'. This is the same thing that GRANT USAGE does.
The following example adds a user custom who can connect from hosts localhost, server.domain, and whitehouse.gov. He wants to access the bankaccount database only from localhost, the expenses database only from whitehouse.gov, and the customer database from all three hosts. He wants to use the password stupid from all three hosts.
To set up this user's privileges using GRANT statements, run these commands:
shell> mysql --user=root mysql
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON bankaccount.*
TO custom@localhost
IDENTIFIED BY 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON expenses.*
TO custom@whitehouse.gov
IDENTIFIED BY 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON customer.*
TO custom@'%'
IDENTIFIED BY 'stupid';
The reason that we do to grant statements for the user 'custom' is that we want the give the user access to MySQL both from the local machine with Unix sockets and from the remote machine 'whitehouse.gov' over TCP/IP.
To set up the user's privileges by modifying the grant tables directly, run these commands (note the FLUSH PRIVILEGES at the end):
shell> mysql --user=root mysql
mysql> INSERT INTO user (Host,User,Password)
VALUES('localhost','custom',PASSWORD('stupid'));
mysql> INSERT INTO user (Host,User,Password)
VALUES('server.domain','custom',PASSWORD('stupid'));
mysql> INSERT INTO user (Host,User,Password)
VALUES('whitehouse.gov','custom',PASSWORD('stupid'));
mysql> INSERT INTO db
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv)
VALUES
('localhost','bankaccount','custom','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv)
VALUES
('whitehouse.gov','expenses','custom','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv)
VALUES('%','customer','custom','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;
The first three INSERT statements add user table entries that allow user custom to connect from the various hosts with the given password, but grant no permissions to him (all privileges are set to the default value of 'N'). The next three INSERT statements add db table entries that grant privileges to custom for the bankaccount, expenses, and customer databases, but only when accessed from the proper hosts. As usual, when the grant tables are modified directly, the server must be told to reload them (with FLUSH PRIVILEGES) so that the privilege changes take effect.
If you want to give a specific user access from any machine in a given domain, you can issue a GRANT statement like the following:
mysql> GRANT ...
ON *.*
TO myusername@"%.mydomainname.com"
IDENTIFIED BY 'mypassword';
To do the same thing by modifying the grant tables directly, do this:
mysql> INSERT INTO user VALUES ('%.mydomainname.com', 'myusername',
PASSWORD('mypassword'),...);
mysql> FLUSH PRIVILEGES;
You can also use xmysqladmin, mysql_webadmin, and even xmysql to insert, change, and update values in the grant tables. You can find these utilities in the Contrib directory of the MySQL Website.
LOAD DATA INFILE example - importing data from csv into mysql
LOAD DATA INFILE "/home/mysql/data/my_table.csv" INTO TABLE my_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
Installing MySQL
The basic commands you must execute to install a *MySQL* source
distribution are:
shell> groupadd mysql
shell> useradd -g mysql mysql
shell> gunzip < mysql-VERSION.tar.gz | tar -xvf -
shell> cd mysql-VERSION
shell> ./configure --prefix=/usr/local/mysql
shell> make
shell> make install
shell> scripts/mysql_install_db
shell> chown -R root /usr/local/mysql
shell> chown -R mysql /usr/local/mysql/var
shell> chgrp -R mysql /usr/local/mysql
shell> cp support-files/my-medium.cnf /etc/my.cnf
shell> /usr/local/mysql/bin/safe_mysqld --user=mysql &
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 !
This is done with:
./bin/mysqladmin -u root -p password 'new-password' (note that new-password *IS* the new root password you wanna assign, and initially there is *NO* password for mysql so when it asks for password: just hit ENTER)
./bin/mysqladmin -u root -h server-name -p password 'new-password'
distribution are:
shell> groupadd mysql
shell> useradd -g mysql mysql
shell> gunzip < mysql-VERSION.tar.gz | tar -xvf -
shell> cd mysql-VERSION
shell> ./configure --prefix=/usr/local/mysql
shell> make
shell> make install
shell> scripts/mysql_install_db
shell> chown -R root /usr/local/mysql
shell> chown -R mysql /usr/local/mysql/var
shell> chgrp -R mysql /usr/local/mysql
shell> cp support-files/my-medium.cnf /etc/my.cnf
shell> /usr/local/mysql/bin/safe_mysqld --user=mysql &
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 !
This is done with:
./bin/mysqladmin -u root -p password 'new-password' (note that new-password *IS* the new root password you wanna assign, and initially there is *NO* password for mysql so when it asks for password: just hit ENTER)
./bin/mysqladmin -u root -h server-name -p password 'new-password'
Subscribe to:
Posts (Atom)