Posteet: mysql

Resetting a forgotten MySQL root password

sox — Sun, 27 Mar 2011 13:04:08 +0200

First of all you will need to ensure that your database is stopped:

root@steve:~# /etc/init.d/mysql stop

Now you should start up the database in the background, via the mysqld_safe command:

root@steve:~# /usr/bin/mysqld_safe --skip-grant-tables &
[1] 6702
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6763]: started

Here you can see the new job (number "1") has started and the server is running with the process ID (PID) of 6702.

Now that the server is running with the --skip-grant-tables flag you can connect to it without a password and complete the job:

root@steve:~$ mysql --user=root mysql
Enter password:

mysql> update user set Password=PASSWORD('new-password-here') WHERE User='root';
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

mysql> exit
Bye

Now that you've done that you just need to stop the server, so that you can go back to running a secure MySQL server with password restrictions in place. First of all bring the server you started into the foreground by typing "fg", then kill it by pressing "Ctrl+c" afterwards.

This will now allow you to start the server:

root@steve:~# /etc/init.d/mysql start
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..

Now everything should be done and you should have regained access to your MySQL database(s); you should verify this by connecting with your new password:

root@steve:~# mysql --user=root --pass=new-password-here
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 5 to server version: 5.0.24a-Debian_4-log

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> exit
Bye

[mysql] [password]

MySQL permissions for backup

spirit — Sat, 19 Jun 2010 18:21:25 +0200

GRANT SHOW DATABASES, SELECT, LOCK TABLES, RELOAD ON *.* to backup@localhost IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

[backups] [mysql] [readonly] [security] [user]

query - case sensitive en mysql

macks — Fri, 28 May 2010 17:44:37 +0200

SELECT *  FROM `table`  WHERE BINARY `column` = 'value'

[mysql]

mysql - guardar los resultados en un fichero

macks — Wed, 05 May 2010 00:44:05 +0200

SELECT id,title INTO OUTFILE "c:/result.text"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "n"
FROM property;

[mysql] [querys] [tips]

Restorer plus rapidement un dump MySQL (multi core)

spirit — Sat, 24 Apr 2010 01:58:27 +0200

find -print0 | xargs -0 -n 1 -P 4 -I {} sh -c "zcat '{}' | mysql mydatabase"

[backups] [find] [mysql] [performances] [restore]

non-interactive mysql install on Debian / Ubuntu

spirit — Mon, 22 Mar 2010 23:49:11 +0100

echo mysql-server mysql-server/root_password select PASSWORD | debconf-set-selections
echo mysql-server mysql-server/root_password_again select PASSWORD | debconf-set-selections

aptitude -y install mysql-server libmysqlclient15-dev

[automatic] [debian] [mysql] [non-interactive] [script] [shell]

manipular datos de una bd externa con django

macks — Thu, 04 Mar 2010 21:19:49 +0100

from django.db import load_backend, transaction, connection

#manipular datos de una bd externa

def sincronizeDB(self):
        myBackend = load_backend('mysql') # or 'mysql', 'sqlite3', 'oracle'
        myConnection = myBackend.DatabaseWrapper({
            'DATABASE_HOST': '192.168.1.11',
            'DATABASE_NAME': 'agenciaperu_local',
            'DATABASE_OPTIONS': {},
            'DATABASE_PASSWORD': "",
            'DATABASE_PORT': "",
            'DATABASE_USER': "root",})
        # Now we can do all the standard raw sql stuff with myConnection.
        myCursor = myConnection.cursor()        
        id = 22
        name = "tecnologia para jos"
        slug = "tecnologia_para_jos"
        row = myCursor.execute("INSERT INTO category(name, slug )  values(%s,%s);", [name, slug])
        row = myConnection._commit()
        #row = transaction.rollback_unless_managed()  -----> sería cuando trabajamos en local

# select simple
        #row = myCursor.execute("select *from category where id = %s and highlight = %s;",[id,0])
        myCursor.fetchall()

[django] [mysql] [python] [soluciones]

ver la version mysql

macks — Thu, 20 Aug 2009 00:30:39 +0200

#terminal
C:\>mysql -uroot -p
mysql> select version();

# cliente mysql
select version();

[bash] [mysql]

agregar una columna a una tabla de la BD

macks — Thu, 13 Aug 2009 23:56:23 +0200

alter table media add column  status varchar (20) after component

[mysql]

dump y restaurar base de datos - Mysql

macks — Tue, 11 Aug 2009 07:10:48 +0200

#dump
mysqldump -hlocalhost -uuser -ppassword nomdatabase> nomdatabase.sql

#restore
mysql -hlocalhost -uuser -ppassword nomdatabase < nomdatabase

[bash] [mysql]

obtener la diferencia entre 2 fechas - Mysql

macks — Fri, 07 Aug 2009 23:55:48 +0200

SELECT TIMESTAMPDIFF(DAY,'2005-01-02','2006-01-08');

[mysql]

Backup d'une database MySQL

benoitbalon — Mon, 27 Jul 2009 19:19:18 +0200

mysqldump -h localhost -u user -ppassword -r/tmp/mysql_backup.data nomdatabase

mysql -h localhost -u user -ppassword nomdatabase < /tmp/mysql_backup.data

[backup] [MySQL]

Pour lister les bases de données sur un serveur

cyo — Wed, 06 May 2009 11:58:01 +0200

mysql -u root -p
mysql> show databases;

[liste] [mysql]

Pour optenir des infos sur une base de données en ligne de commande (notamment l'encodage utilisé)

cyo — Wed, 06 May 2009 11:56:23 +0200

mysql -u root -p -D nom_de_la_base
mysql> status;

[encodage] [mysql]

Distance between two points (given the latitude/longitude of those points) FUNCTION for MySQL

spirit — Mon, 09 Feb 2009 20:07:38 +0100

Great circle distance
Find the distance in kilometres between two points on the surface of the earth. This is just the sort of problem stored functions were made for. For a first order approximation, ignore deviations of the earth's surface from the perfectly spherical. Then the distance in radians is given by a number of trigonometric formulas. ACOS and COS behave reasonably:

             COS(lat1-lat2)*(1+COS(lon1-lon2)) - COS(lat1+lat2)*(1-COS(lon1-lon2))
rads = ACOS( --------------------------------------------------------------------- )
                                              2

We need to convert degrees latitude and longitude to radians, and we need to know the length in km of one radian on the earth's surface, which is 6378.388. The function:

set log_bin_trust_function_creators=TRUE;

DROP FUNCTION IF EXISTS GeoDistKM;
DELIMITER |
CREATE FUNCTION GeoDistKM( lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT ) RETURNS float
BEGIN
  DECLARE pi, q1, q2, q3 FLOAT;
  DECLARE rads FLOAT DEFAULT 0;
  SET pi = PI();
  SET lat1 = lat1 * pi / 180;
  SET lon1 = lon1 * pi / 180;
  SET lat2 = lat2 * pi / 180;
  SET lon2 = lon2 * pi / 180;
  SET q1 = COS(lon1-lon2);
  SET q2 = COS(lat1-lat2);
  SET q3 = COS(lat1+lat2);
  SET rads = ACOS( 0.5*((1.0+q1)*q2 - (1.0-q1)*q3) ); 
  RETURN 6378.388 * rads;
END;
|
DELIMITER ;

-- toronto to montreal (505km):
select geodistkm(43.6667,-79.4167,45.5000,-73.5833);
+----------------------------------------------+
| geodistkm(43.6667,-79.4167,45.5000,-73.5833) |
+----------------------------------------------+
|                           505.38836669921875 |
+----------------------------------------------+

[distance] [function] [lat] [latitude] [lng] [longitude] [maps] [mysql]