Datos y Software - Soluciones

Datos y Software - Soluciones

 

Para arrancarlo en windows:
mysqld-shareware para arrancar el daemond
y el cliente: mysql -u gitzu -p (password= 952discs)
mysql -u root -p (password= diferent)
etc

El programa mysql front, es como un DBD32 pero para mysql
Si es en en el modo grafico colocar user y la contraseña despues de
haber arrancado el daemond.

WinAdminMySQL es similar pero menos completo y depende del archivo
\windows\my.ini

Con password colocado para el root es:
mysqld-shareware y arranca sin especificar user ni password
En linux solo lo puede arrancar el root que es el dueño del archivo y el
unico con derechos de ejecución.
Pero para qu 

e un cliente se conecte debe usar -u "user" -p , ej:
mysql -u root -p
mysqladmin -u root -p shutdown

Para cambiar la contraseña del root: use mysql y entonces:
update user set password=password("diferent") where user="root";
o mysqladmin -u root password "new_password"

Si desde un programa trato de crear una tabla y sale algo como:
IOT trap abort
Lo mas seguro es que el usuario no tenga permiso para crear tablas,
hay que poner 'Y' en el campo de user correspondiente o darle los derechos con
grant.
Notar como mysql es un programa que si maneja los errores y saca
mensajes adecuados.

Si algún día hay problemas para entrar con el password, este se puede
borrar de la tabla users.isd utilizando un editor hexadecimal, y todo
vuelve a la normalidad.
Probar arrancar el daemond con -Sg para que skip grant tables.

Cuando hau un error en un query y dice que esta near a tal cosa es justo
a la izquierda de lo que aparece.

SOBRE LOS PROGRAMAS DE BIN:

No hay un programa en windows centralizado que maneje todo.
Tomado del manual:

-----------------------------------------------------------------------
All MySQL clients that communicate with the server using the mysqlclient
library use the following environment variables:

Name Description
MYSQL_UNIX_PORT The default socket. Used with 'localhost'.
MYSQL_TCP_PORT The default TCP port.
MYSQL_PWD The default password.
MYSQL_DEBUG Debug-trace options when debugging.

All MySQL programs take many different options.
Use the switch --help to get a full description of the different options.
Try for example mysql --help.

mysql
mysql is a simple SQL shell (with gnu readline).
It supports interactive use and non-interactive querying. When used
interactively, the result is given in an ascii-table format, but when used
as a filter the result is a tab-separated output (this as other things can
be changed with startup options). You can simply run scripts with mysql
database < script.sql > output.tab. If you have a problem with that the
memory in the client is not enough, use the --quick switch! This will force
mysql to use mysql_use_result() instead of mysql_store_result() to retrieve
the result set.
Recibe todos los comandos SQL, es una especie de
shell interactivo. (help da ayuda sobre como se maneja pero no sobre las
instrucciones SQL capaz de manejar), ej create table, select from etc.
Toda orden SQL debe terminar en ';'

mysqlaccess
Script to check the privileges for a host, user and database combination.

mysqladmin
Administration utility. Create/Drop databases, reload (read new users) and
refresh (flush tables to disk, reopen log files). Also gives version,
process and status information from the server.
Crea databases (Directorios pegados de \mysql\data
Se le pasan como parametros en la linea de órdens,
ej: mysqladmin create NuevaCarpeta. Tiene mas opciones, en general es el
administrador del server sql.

mysqld
The SQL daemon. This should always be running.
Para quitarlo: bin\mysqladmin -u root shutdown
Si no se quita no queda grabado nada, según parece y se puede bloquear
W95 si se cierra la sesión de dos donde él estaba.

mysqldump
Dump a MySQL database into a file with SQL statements or tab separated text
files. Enhanced freeware originally by Igor Romanenko.

mysqlimport
Imports one or more text files into respective tables. Can use all formats
supported by LOAD DATA INFILE. See section 7.14 LOAD DATA INFILE syntax

mysqlshow
Shows information about databases, tables, columns and indexes.

mysqlbug
This script should always be used when filing a bug report to the MySQL list.

mysql_install_db
Creates the MySQL grant tables with default privileges. This is usually
only executed once when installing the first MySQL release on a new system.

isamchk
Check, optimise and repair MySQL tables.

mysqlcheck
Chequea y repara databases o tablas. Ej: mysqlcheck -r -A repara todas las databases.

make_binary_release
Makes a binary release of a compiled MySQL. This could be sent by ftp to
/pub/mysql/Incoming on www.tcx.se for the convenience of other MySQL users.

msql2mysql
A shell script to convert a mSQL program to MySQL. Doesn't handle all cases
but gives a good start when converting.

replace
Binary used for msql2mysql. Utility program to change strings in place in
files or on stdin. Uses a finite state machine to match longer strings first.
Can be used to swap strings, for example replace a b b a -- files swaps a
and b in the given files.

safe_mysqld
Starts the mysqld daemon with some safety features. Restarts on error and
has logging of runtime information to a log file.

mysqld: El daemon que tiene que estar corriendo para que lo demas
funcione.
------------------------------------------------------------------------

mysqlmanager: igual que mysql pero en w95

SOBRE LAS TABLAS

Al crear una tabla se crean 3 archivos:
*.frm El descriptor de la tabla
*.isd El archivo de datos
*.ism El indice

Todo sobre como es la conexión con mysql está en la sección del manual:
6.9 Access control, stage 1: Connection verification
Wilcards: % representa cualquier host, puede se utilizado en el campo
host de la tabla users

6.13 ADDING NEW USER PRIVILEGES TO MYSQL

You can add users two different ways: by using GRANT statements or by
manipulating the MySQL grant tables directly. The preferred method is to
use GRANT statements, because they are more concise and less
error-prone.

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 'something' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@"%"
IDENTIFIED BY 'something' 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 ('something' 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
thuse 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 set up 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('something'),
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y')
mysql> INSERT INTO user VALUES('%','monty',PASSWORD('something'),
'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 3.22.11 had fewer privilege
columns). For the admin user, the more readable extended INSERT syntax
that is available starting with 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';

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 at the
MySQL Contrib directory

Do mysql -u root. If you granted a connection without asking password,
then this is bad.
Use command SHOW GRANTS and check who is having access
and to what.
Users of MySQL C API: Check out the mysql_escape() API
call.
Use passwords for all MySQL users. Remember that anyone can log in
as any other person as simply as mysql -u other_user db_name if
other_user has no password.
It is common behavior with client/server applications that the client
may specify any user name. You can change the password of all users by
editing the mysql_install_db script before you run it, or only the
password for the MySQL root user like this:
shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password') WHERE
user='root';
mysql> FLUSH PRIVILEGES;

Don't run the MySQL daemon as the Unix root user. mysqld can be run as
any user. You can also create a new Unix user mysql to make everything
even more secure. If you run mysqld as another Unix user, you don't need
to change the root user name in the user table, because MySQL user names
have nothing to do with Unix user names. You can edit the mysql.server
script to start mysqld as another Unix user. Normally this is done with
the su command. For more details, see section 19.8 How to run MySQL as a
normal user. If you put a password for the Unix root user in the
mysql.server script, make sure this script is readable only by root.
Check that the Unix user that mysqld runs as is the only user with
read/write privileges in the database directories. Don't give the
process privilege to all users.
The output of mysqladmin processlist shows the text of the currently
executing queries, so any user who is allowed to execute that command
might be able to see if another user issues an UPDATE user SET
password=PASSWORD('not_secure') query. mysqld saves an extra connection
for users who have the process privilege, so that a MySQL root user can
log in and check things even if all normal connections are in use. Don't
give the file privilege to all users. Any user that has this privilege
can write a file anywhere in the file system with the privileges of the
mysqld daemon! To make this a bit safer, all files generated with SELECT
... INTO OUTFILE are readable to everyone, and you can't overwrite
existing files. The file privilege may also be used to read any file
accessible to the Unix user that the server runs as. This could be
abused, for example, by using LOAD DATA to load `/etc/passwd' into a
table, which can then be read with SELECT. If you don't trust your DNS,
you should use IP numbers instead of hostnames in the grant tables. In
principle, the --secure option to mysqld should make hostnames safe. In
any case, you should be very careful about using hostname values that
contain wildcards! The following mysqld options affect security:

The following mysqld options affect security:

--secure IP numbers returned by the gethostbyname() system call are
checked to make sure they resolve back to the original hostname. This
makes it harder for someone on the outside to get access by simulating
another host. This option also adds some sanity checks of hostnames. The
option is turned off by default in MySQL 3.21 since it sometimes takes a
long time to perform backward resolutions. MySQL 3.22 caches hostnames
and has this option enabled by default. --skip-grant-tables This option
causes the server not to use the privilege system at all. This gives
everyone full access to all databases! (You can tell a running server to
start using the grant tables again by executing mysqladmin reload.)
There are several distinctions between the way user names and passwords
are used by MySQL, and the way they are used by Unix or Windows:

User names, as used by MySQL for authentication purposes, have nothing
to do with Unix user names (login names) or Windows user names. Most
MySQL clients by default try to log in using the current Unix user name
as the MySQL user name, but that is for convenience only. Client
programs allow a different name to be specified with the -u or --user
options. This means that you can't make a database secure in any way
unless all MySQL user names have passwords. Anyone may attempt to
connect to the server using any name, and they will succeed if they
specify any name that doesn't have a password. MySQL user names can be
up to 16 characters long; Unix user names typically are limited to 8
characters. MySQL passwords have nothing to do with Unix passwords.
There is no necessary connection between the password you use to log in
to a Unix machine and the password you use to access a database on that
machine. MySQL encrypts passwords using a different algorithm than the
one used during the Unix login process. See the descriptions of the
PASSWORD() and ENCRYPT() functions in section 7.4.12 Miscellaneous
functions. Note: Specifing a password on the command line is not secure!
Any user on your system may then find out your password by typing a
command like: ps auxww. See section 4.15.4 Option files.

mysql uses default values for connection parameters that are missing from the
command line:

The default hostname is localhost.
The default user name is your Unix login name.
No password is supplied if -p is missing.
Store your password in a configuration file. For example, you can list
your password in the [client] section of the `.my.cnf' file in your home
directory:
[client]
password=your_pass

If you store your password in `.my.cnf', the file should not be group or
world readable or writable. Make sure the file's access mode is 400 or
600. See section Use a -p or --password option (with no your_pass value
specified). In this case, the client program solicits the password from
the terminal: shell> mysql -u user_name -p
Enter password: ********

With the registered MySQL version you can easily add new users and
change privileges with GRANT and REVOKE commands. See section 7.26 GRANT
and REVOKE syntax. With the Windows shareware version on has to use
INSERT, UPDATE and DELETE one the tables in the mysql database to manage
users and their privileges. See section 6.15 Causes of Access denied
errors

PARA ADICIONAR O CREAR (si no existe) DERECHOS A UN USUARIO SOBRE UNA BASE DE
DATOS:

Para crear una nueva base de datos (directorio) es con mysqladmin create tal

Al estilo picapiedra:
Al crear un usuario con insert en la tabla user y luego especificar cual base
de datos puede acceder haciendo otro insert en la tabla db, resultó que no
tenía acceso a la base de datos. Toca ponerle al menos el campo
Select_priv='y'.
Para adicionar un usuario,Sin password: insert into
user (Host,User,Password) values('localhost','dummy','');
Con password: insert into user (Host,User,Password) values('localhost','dummy',password("3dffcv"));
insert into db al usuario con la db para que la pueda usar.

Par darle derechos a un usuario sobre una base de datos:
grant all privileges on gitzu.* to gitzu@localhost identified by 'password'
grant select, update " " " " " " " " "

Tambien se puede especificar al usuario solo con gitzu sin el host
Notar que para una base de datos es gitzu.*
Para una sola tabla es gitzu, sin el asterisco
Se puede verificar con select * from db
y mirando los derechos del usuario. Ojo una cosa es db y otra es la tabla user.
db tiene los usuarios de las bases de datos
user tiene los usuarios de las tablas

Mas ejemplos con mas detalles:
CREA el usuario fvel, con password eureka y se puede conectar desde donde sea:
grant all privileges on ucc.* to fvel@"%" identified by 'eureka' with
grant option;
Asi fvel tambien puede entrar desde el local host, pero sin password, para que
entre con password, escribir:
grant all privileges on ucc.* to fvel@localhost identified by 'eureka'
with grant option;
Para BORRAR el usuario fvel:
como root:
use mysql
delete from user where user='fvel';
Ver en el manual:
"adding new users"
grant

Para que un usuario pueda utilizar select into outfile... debe tener
derechos de FILE, que se le colocan así:
grant file on ucc.* to fvel;
Con la siguiente particularidad, el select debe ser:
select * into outfile 'salida.txt'; sin especificar una ruta.
El archivo es creado en /va/lib/mysql/ucc, es decir, donde esta la
database, y ahí todo es usuario mysql y grupo mysql.
Para crearlo local ver abajo "PARA SACAR LA TABLA A UN ARCHIVO TEXTO:"

Para poner contraseña al root:
./bin/mysqladmin -u root password "new password"

Para chequear al daemond:
./bin/mysqladmin version

Para configurarlo: (Si se instala del source y no binaria)
./configure al instalarlo

Para arrancarlo o pararlo: mysql.server start o stop

mysql++1.7 necesita: ciertas librerias omo libc.so.6, ld-linux.so.2 etc
que la versión de suse 5.1 no las tiene.

privilege system in mysql:
Se tienen asociados a host+username, privilegios para select, insert,
update en una database.
También se puede como anonumous
Los privilegios son guardados en tres tablas: user, host(las máquinas) y
db.
Si se cambian las tablas, mysqladmin reload para que tenga efecto.

use database, no la tabla. Database es el directorio que tiene las
tablas.

Para arrancar el demonio en boot time:
Editar /etc/rc.d/rc.local
Y colocar /usr/local/mysql/bin/safe_mysqld --user=mysql &
en la última línea.

PARA EXPORTAR TABLAS DE UN SERVIDOR A OTRO:
En el origen hacer: mysqldump --opt database >backup-file.sql
En el destino: mysql (con -u -p segun corresponda) database < backup-file.sql
Así de sencillo!!!

PARA SACAR LA TABLA A UN ARCHIVO TEXTO:
mysql -p -e 'select tal cosa' ucc >archivo.txt
utilidad parametros base de datos archivo a donde va
Con esto puede tener un txt en una carpeta local.

- Al tratar de conectarme a MySQL, sacaba el mensaje:
"Lost connection to MySQL server during query"
Una causa es que para conectarse el host tenía una IP, al cambiarlo por
"" o por localhost, se arregló. Creó que mi configuración de mysql
estaba coja, pero otra tomada de la comunidad en la página de MySQLFront fué:
em /etc/my.cfg
Añadir la línea skip-name-resolve en la sección [mysqld]
Notar que fué en un redhat que al arrancar informaba de problemas con el hostname.
En Mysql 5.0 para permitir conexiones con TCP-IP (remotas) y no solo con los sockes de unix,
comentarizar bind-address=127.0.0.1 en my.cfg