Herramientas de usuario

Herramientas del sitio


notas:bases_de_datos

Diferencias

Muestra las diferencias entre dos versiones de la página.

Enlace a la vista de comparación

Ambos lados, revisión anteriorRevisión previa
Próxima revisión
Revisión previa
notas:bases_de_datos [2010/04/07 16:46] cayunotas:bases_de_datos [2013/09/13 14:21] (actual) – [Replicacion facil] cayu
Línea 6: Línea 6:
 ==== Perl DBI ==== ==== Perl DBI ====
  
- +^**MySQL**^**PostgreSQL**^
-^^**MySQL**^**PostgreSQL**^^+
 |$db=DBI->connect("dbi:mysql:database= ... )|$db=DBI->connect("dbi:Pg:database= ... )| |$db=DBI->connect("dbi:mysql:database= ... )|$db=DBI->connect("dbi:Pg:database= ... )|
  
Línea 13: Línea 12:
 ==== Sintaxis y lenguaje ==== ==== Sintaxis y lenguaje ====
  
- +^**MySQL**^**PostgreSQL**^
-^^**MySQL**^**PostgreSQL**^^+
 | SHOW FULL PROCESSLIST / SHOW PROCESSLIST | SELECT * FROM pg_stat_activity | | SHOW FULL PROCESSLIST / SHOW PROCESSLIST | SELECT * FROM pg_stat_activity |
 | # | -- | | # | -- |
Línea 27: Línea 25:
 ==== Tipos de datos ==== ==== Tipos de datos ====
  
-^^MySQL^PostgreSQL^comments^^+^MySQL^PostgreSQL^comments^
 |<code> |<code>
 TINYINT TINYINT
Línea 134: Línea 132:
 show tables like 'ifvolume_1%' show tables like 'ifvolume_1%'
 </code> </code>
 +
 +==== Replicacion facil ====
 +
 +<code>
 +[mysqld]
 +datadir=/var/lib/mysql
 +socket=/var/lib/mysql/mysql.sock
 +
 +old_passwords=1
 +log_bin = /var/log/mysql/mysql-bin.log
 +relay-log = /var/log/mysql/mysql-relay.log
 +max_binlog_size = 100M
 +
 +expire_logs_days        = 2
 +max_binlog_size         = 100M
 +
 +log-bin
 +binlog-do-db=proxyauth
 +skip-slave-start
 +
 +server-id=1
 +replicate-do-db=base
 +replicate-do-table=base.tabla
 +
 +master-host=10.1.1.1
 +master-user=root
 +master-password=contraseña
 +master-port=3306
 +
 +[mysql.server]
 +user=mysql
 +basedir=/var/lib 
 +</code>
 +
 +<code>
 +[mysqld]
 +datadir=/var/lib/mysql
 +socket=/var/lib/mysql/mysql.sock
 +
 +old_passwords=1
 +log_bin = /var/log/mysql/mysql-bin.log
 +relay-log = /var/log/mysql/mysql-relay.log
 +max_binlog_size = 100M
 +
 +expire_logs_days        = 2
 +max_binlog_size         = 100M
 +
 +log-bin
 +binlog-do-db=proxyauth
 +skip-slave-start
 +
 +server-id=2
 +replicate-do-db=base
 +replicate-do-table=base.tabla
 +
 +master-host=10.1.1.2
 +master-user=root
 +master-password=contraseña
 +master-port=3306
 +
 +[mysql.server]
 +user=mysql
 +basedir=/var/lib 
 +</code>
 +
 +**skip-slave-start** //Es para tener que hacer el start slave; manualmente (por si las dudas).//
 +
 +
 +
 +==== Importar/Exportar CSV ====
 +Importacion :
 +<code sql>
 +LOAD DATA LOCAL INFILE 'usuarios.csv'
 +INTO TABLE usuarios
 +FIELDS TERMINATED BY ','
 +ENCLOSED BY '"'
 +LINES TERMINATED BY '\n'
 +(nombre, pass, usuario);
 +</code>
 +
 +Exportacion :
 +<code sql>
 +SELECT * FROM usuarios ORDER BY date DESC
 +INTO OUTFILE "usuarios.csv"
 +FIELDS TERMINATED BY ','
 +ENCLOSED BY '"'
 +LINES TERMINATED BY '\r\n';
 +</code>
 +
 +<code bash>
 +mysql -u root -p  cdr -B -e  "select * from \`calldetails\` limit 2;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g'
 +</code>
 +===== Oracle =====
 +
 +
 +==== Oracle XE issues ====
 +
 +Oracle xe must be the buggiest free counterpart of a commercial product ever released.Here's a list of issues and solutions encountered on Ubuntu 10.04 so far.Few config tips first.
 +
 +  * I don't set up it to be started upon boot (don't want this bag of bugs eat my memory)
 +  * change the default apex port from 8080 to smth else
 +  * use 8+ characters for sys/system password
 +  * check status of the DB: $ sudo /etc/init.d/oracle-xe status
 +  * start the DB - I use restart just to be sure: $ sudo /etc/init.d/oracle-xe restart
 +
 +Q: After installing and sourcing /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh I was getting /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/nls_lang.sh: 114: %%[[%%: not found each time starting a shell
 +
 +
 +A: Change the nls_lang.sh to use your shell %%(/%%bin/bash) instead of /bin/sh on top of the script.
 +
 +
 +Q: after installing oracle xe I cannot login to apex (oracle web admin) under sys or system. It won't accept my login/password. Keeps saying access denied although I'm sure the password is correct (the one used during /etc/init.d/oracle-xe configure)
 +
 +
 +A: Use a longer password! It won't complain but seems like it doesn't like short passwords. Use at least 8 chars.Q:After some time your oracle connected application runs into
 +
 +
 +> ORA-12519, TNS:no appropriate service handler found
 +
 +
 +A: Run the following statement from sqlplus or web admin (you should have it accessible due to above tips)
 +
 +
 +> ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE;
 +
 +
 +Found [[http://en.newinstance.it/2007/06/01/ora-12519-tnsno-appropriate-service-handler-found/|here]]
 +
 +
 +Q: How to remove oracle user from ubuntu desktop login screen.
 +
 +A: Change the oracle's UID to smth below 1000.
 +
 +
 +$ sudo usermod --gid 999 oracle
 +
 +After that change the ownership of the files in /var/tmp/.
 +
 +oracle$ sudo chown -R oracle:dba /var/tmp/.oracle/
 +
 +or you won't be able to start the DB anymore :)
 +
 +
 +Q: Cannot open Oracle web interface, Page cannot be displayed. Used to work before, and the DB is working.
 +
 +
 +A: Might happen especially if you selected to start the DB manually rather than on each boot. The symptoms might be a bit different, the DB might be started or might be not. Some services just fail to start.
 +
 +Try restart : 
 +
 +$ sudo /etc/init.d/oracle-xe restart or $ sudo /etc/init.d/oracle-xe force-reload
 +
 +
 +Fuente: http://ubuntu-answers.blogspot.com/2010/10/oracle-xe-issues.html
notas/bases_de_datos.1270658777.txt.gz · Última modificación: 2010/04/07 16:46 por cayu